Totals not working when measure with IF

[AgnihotramKris]

Hi All,

I have a measure with IF as like below.

MTD = IF([ABC]="XYZ", measure1, measure2)

When I use MTD in the matrix all values are showing correctly. But when I add row totals immediately giving error, saying multiple values provided when sigle value is expected.

If I change my measure as MTD = measure2 then totals are coming perfectly fine, but I want to use above if condition or switch to get my desired result.

Not sure what is the problem. Can someone please guide me how to go ahead.

[AgnihotramKris]

I got the answer.. actually I used MTD = IF(VALUES(ABC) ="XYZ", measure 1, measure 2).

I am supposed to used IF(SELECTEDVALUE(ABC)="XYZ", measure 1, measure 2)

[Anonymous · Accepted solution]

Hi @AgnihotramKris ,

IF() function will calculate result based on [ABC], subtotal in matrix does not has a value in [ABC], so you won't get total in martix subtotal.

Here I suggest you to create a new measure based on this one by SUMX().

MTD = IF([ABC]="XYZ", measure1, measure2)

New Measure:

SUMX = SUMX('TABLE',[MTD])

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[latimeria]

Hi,

It is not clear what you try to achieve.

My understanding is that you want a measure for details and another one for totals.

In this case, have a look at isinscope - https://dax.guide/isinscope/ 

MTD = if( isinscope [detail row column], , measure1, measure2)

[amitchandak]

@AgnihotramKris , In a measure you can use measure or aggregated values

MTD = IF(Max(Table[ABC]) ="XYZ", measure1, measure2)