powerbidaxdata-analysispowerbi-desktopbusiness-intelligence

Calculated table with aggregation DAX


Im trying to create a calculated table from 2 tables that are connected with a bridge.

My 3 tables

Report      ReportRow   Account Sign UniqueKey
Rapport129  129/* CM    35100   1    4177960
Rapport129  129/* CM    35100   -1   4174475
Rapport129  129/* CM    34000   1    4176966
Rapport129  129/* CM    93220   -1   4179774

UniqueKey Konto
4179774   93220
4177960   35100
4176966   34000
4174475   35100


FactKey Account Amount
1   35100   10
2   35100   20
3   34000   100
4   34000   200
5   93220   -500
6   93220   300

enter image description here

The Model: enter image description here

This is how i want my calculated table to look like:

enter image description here


Solution

  • Table = 
    ADDCOLUMNS(
        SUMMARIZE(DimReport, DimReport[Report], DimReport[ReportRow], DimReport[Account]),
        "Sum Sign", CALCULATE( SUM(DimReport[Sign])),
        "Sum Amount", CALCULATE(SUM('Fact'[Amount]))
    

    )enter image description here