Switch measure not showing correct grund total - Help :)

[Anonymous]

Hello PowerBI community,

I have made this measure, that should give points:

Switch = SWITCH(TRUE(), SUM('Sales Finance'[Revex]) >VALUE(15000000),VALUE(1),

SUM('Sales Finance'[Revex])> VALUE(10000000),VALUE(2),VALUE(0))

The value you recieve should be summerized in the grand total, but the function just gives me 1 in grand total, because it is performering the logical test on the grand total of the revenue.

How can i get past this?

I have seen alot of posts regarding fixing the grand totalt, but not in context with the Switch function, please advice 🙂

[Pragati11]

Hi @Anonymous ,

It looks like your SWITCH statement is not right.

Switch = SWITCH(TRUE(), SUM('Sales Finance'[Revex]) >VALUE(15000000),VALUE(1),

SUM('Sales Finance'[Revex])> VALUE(10000000),VALUE(2),VALUE(0))

The highlighted condition will give everything a value = 1 as it is in 1st position in your switch statement; also your 2nd condition will return 1st condition output in the scenario where a value is > VALUE(10000000) and >VALUE(15000000)

Try writing BETWEEN statements for comparison. Something like:

Sales >= 10000 && Sales <= 50000 then VALUE1

Thanks,

Pragati

[Greg_Deckler · Accepted solution]

@Anonymous This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[amitchandak]

@Anonymous , the formula is not clear. It can be

sumx('Sales Finance', if('Sales Finance'[Revex] >15000000,1 ,'Sales Finance'[Revex]> 10000000, 2,0))

or with some group by, first group and then compare, assumed -Aggcol

sumx(values('Sales Finance'[Aggcol]), if(sum('Sales Finance'[Revex]) >15000000,1 ,sum('Sales Finance'[Revex])> 10000000, 2,0))

Can you share sample data and sample output in table format?