Switch Measure Not Showing Grand Total


#1

Hi,

I seem to be having an issue with a switch statement, where the measure will will not give me a grand total.

My DAX measures and a screenshot is attached.

Thanks in advance

Adviser Has Target = SELECTEDVALUE('Access Adviser Appointments Table'[AdviserTarget])

All Adviser Actual Capacity = CALCULATE(SUM('Access Adviser Appointments Table'[ActualTarget]),
                                FILTER('Access Adviser Appointments Table',
                                    'Access Adviser Appointments Table'[AdviserTarget]="Yes"))

All Adviser Seen Appts = [All Adviser Total Appts]-[All Adviser Cancelled Appts]

All Adviser Capacity = SWITCH(TRUE(),
                        [Adviser Has Target]="Yes",[All Adviser Actual Capacity],
                            [Adviser Has Target]="No", [All Adviser Seen Appts]
                    )

image


#2

The reason it’s not showing anything is because you have placed no value in the formula for when there is no ‘yes’ or ‘no’ result (ie. the total). You have left it blank right at the end.

To fix this quickly I would create another formula using this pattern and it should solve the total issue for you

Iteration Demo 1 = 
SUMX( 
   SUMMARIZE( 'Access Adviser Appointments Table', 'Access Adviser Appointments Table'[Advisor],
       "Adviser Capacity", [All Adviser Capacity] ),
            [Adviser Capacity] )

#3

Hi Sam,

Thank you for this. Exactly what I was after.

I left the switch statement intentionally blank at the end as i was not getting the expected result previously.