Forum Discussion

djillbunters's avatar
djillbunters
Frequent Visitor
2 years ago
Solved

Showing calculated column in matrix while showing measures as rows

I'm trying to create a P&L report in PBI Desktop. My dataset (high level) exists of a table with actuals (GLAccount, Amount, Date) and a table with budget (GLAccount, Amount, Date). I combined the ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    djillbunters If I understand this correctly, you will need to use a disconnected table consisting of rows:

    Actual

    Budget

    Delta

     

    Use that as your column header. Then you will need to modify your measures such as:

     

    Turnover = 
  VAR __Category = MAX('DisconnectedTable'[Column1])
  VAR __Result = 
    SWITCH( __Category, 
      "Actual", SUMX(FILTER('Table', [Classification]="Actual"), [Value]),
      "Budget", SUMX(FILTER('Table', [Classification]="Budget"), [Value]),
      SUMX(FILTER('Table', [Classification]="Actual"), [Value]) - SUMX(FILTER('Table', [Classification]="Budget"), [Value])
    )
RETURN
  __Result

    It's essentially a simple version of this: The New Hotness (Custom Matrix Hierarchy) - Microsoft Fabric Community

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
2 years ago

Hi,

One idea would be to create 2 seperate columns (instead of carrying Budget and Actual in the Clasification column) - Budget and Actual in the Query Editor.