Power BI Custom Subtotals

[smitpau]

Hi,

I'd like to be able to create custom subtotals for say Total Revenue based on other values.

I'm using the idea from the video linked below to do this using a SWITCH function.

The irony is the normal values pull through okay, it's the [Total Rev] value that doesn't, which is strange as the card shows the correct value.

How To Create Unique Subtotals In Power BI Tables Using DAX - YouTube

Any help is appreciated.

File is also linked below if it's easier to go through that way, basically the Total Rev figure is 1,000 which doesn't map to the Total Revenue line.

DAX Switch Test.pbix

PBIX File

1drv.ms

My code:

Selected Year =
VAR CurrentItem = SelectedValue (Sheet1[P&L ])
RETURN
SWITCH( TRUE (),
CurrentItem = "Total Revenue",[Total Rev],
calculate ([Values v2],
filter (Sheet1,Sheet1[P&L ]=CurrentItem)))

 

[smitpau]

So to summarise the problem as I may not have explained it well initially.

The switch doesn't switch to a measure for some reason i.e. the first line below works and shows 1,000 but the second line is blank even though the measure is defined.

CurrentItem = "Total Revenue","1000",
CurrentItem = "Total Revenue",[Total Rev],

 

[smitpau]

If anybody has a solution to this I'll happy.

Thanks

 

[smitpau]

Just to round this off, if it helps anyone, it was the measure that was the problem.

The below measure works needed to put in the ALL (Sheet1) bit in the code.

Total Rev = calculate ([Values v2],ALL(Sheet1),Sheet1[P&L ]="Sales 1" )



Source:

Solved: Re: PowerBI Switch DAX Not Working - Microsoft Power BI Community