SWITCH with SUMX measures values not showing up correctly

[Anonymous]

Hello everyone. Posting here is my last resort 😭

So, I have a matrix visual that I would like to show. I'm using SWITCH() measures to show values in the matrix particularly because some of the values come from calculated columns and some from measures.

Values =
VAR ASR = CALCULATE(SUM('FY21 Rev'[Rev]), FILTER(ALLSELECTED('FY21 Rev'), 'FY21 Rev'[Group]="Assurance")) + CALCULATE(SUM('FY21 Rev'[Rev]), FILTER(ALLSELECTED('FY21 Rev'), ISBLANK('FY21 Rev'[Group]))/2)
VAR CONS = CALCULATE(SUM('FY21 Rev'[Rev]), FILTER(ALLSELECTED('FY21 Rev'), 'FY21 Rev'[Group]="Consulting"))
VAR DEALS = CALCULATE(SUM('FY21 Rev'[Rev]), FILTER(ALLSELECTED('FY21 Rev'), 'FY21 Rev'[Group]="Deals"))
VAR TAX = CALCULATE(SUM('FY21 Rev'[FY21 Rev]), FILTER(ALLSELECTED('FY21 Rev'), 'FY21 Rev'[Group]="Tax")) + CALCULATE(SUM('FY21 Rev'[FY21 Rev]), FILTER(ALLSELECTED('FY21 Rev'), ISBLANK('FY21 Rev'[Group]))/2
VAR IFS = CALCULATE(SUM('FY21 Rev'[FY21 Rev]), FILTER(ALLSELECTED('FY21 Rev'), 'FY21 Rev'[Group]="IFS"))

RETURN
SWITCH(
SELECTEDVALUE('Group dim'[LoS Group]),
"Assurance", ASR,
"Consulting", CONS,
"Deals", DEALS,
"Tax", TAX,
"IFS", IFS)

As you can see above, for VARs ASR and TAX they actually have 2 parts to their formulas. The first part is the same as the other VARs but they also have find ISBLANK([Group]) and /2 and sum these 2 parts together.

This measure's values are showing up correctly but there's no row subtotal value. I saw somewhere that I should do a SUMX measure to show the subtota,l which is what I did next.

Values* = SUMX(VALUES('Group dim'[Group]), 'FY21 Rev'[Values])

This is where the issue is. The subtotal shows up correctly BUT the values for VARs ASR and TAX now show only the values of first part of the formulas, leaving out the ISBLANK([Group]) and /2.

Any input/help is appreciated!

Thank you!

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please update the formula of your measure [Values] as below:

Values =

VAR ASR = CALCULATE(SUM('FY21 Rev'[Rev]), FILTER(ALLSELECTED('FY21 Rev'), 'FY21 Rev'[Group]="Assurance")) + CALCULATE(SUM('FY21 Rev'[Rev]), FILTER(ALLSELECTED('FY21 Rev'),

'FY21 Rev'[Group]=""||ISBLANK('FY21 Rev'[Group])))/2

VAR CONS = CALCULATE(SUM('FY21 Rev'[Rev]), FILTER(ALLSELECTED('FY21 Rev'), 'FY21 Rev'[Group]="Consulting"))

VAR DEALS = CALCULATE(SUM('FY21 Rev'[Rev]), FILTER(ALLSELECTED('FY21 Rev'), 'FY21 Rev'[Group]="Deals"))

VAR TAX = CALCULATE(SUM('FY21 Rev'[FY21 Rev]), FILTER(ALLSELECTED('FY21 Rev'), 'FY21 Rev'[Group]="Tax")) + CALCULATE(SUM('FY21 Rev'[FY21 Rev]), FILTER(ALLSELECTED('FY21 Rev'), 'FY21 Rev'[Group]=""||ISBLANK('FY21 Rev'[Group])))/2

VAR IFS = CALCULATE(SUM('FY21 Rev'[FY21 Rev]), FILTER(ALLSELECTED('FY21 Rev'), 'FY21 Rev'[Group]="IFS"))

RETURN

SWITCH(

SELECTEDVALUE('Group dim'[LoS Group]),

"Assurance", ASR,

"Consulting", CONS,

"Deals", DEALS,

"Tax", TAX,

"IFS", IFS)

Best Regards