Power BI - how to all ALL(Table) to a measure - remove all filters

[cghanta]

How can I add ALL(UPS) to the following measure, so the card value will remain the same all the time:
The value is changing when rows of a table is selected.

Measure = VAR Total_Sum = (SUM('UPS'[1st_UPS]) + SUM('UPS'[2nd_UPS]))

VAR Non_Zero_Count = SUMX('UPS',IF('UPS'[1st_UPS] = 0, 0, 1) + IF('UPS'[2nd_UPS] =0, 0, 1))

RETURN

if(Non_Zero_Count=0,0,Total_Sum/Non_Zero_Count)

[v-veshwara-msft · Accepted solution]

Hi @cghanta ,
Thanks for using Microsoft Fabric Community,
To ensure that the measure remains unaffected by selection, you can use the ALL function to remove filters from the 'UPS' table. Try modifying your measure as follows:

Measure =
VAR Total_Sum = SUMX(ALL('UPS'), 'UPS'[1st_UPS] + 'UPS'[2nd_UPS])
VAR Non_Zero_Count = 
    SUMX(
        ALL('UPS'), 
        IF('UPS'[1st_UPS] = 0, 0, 1) + IF('UPS'[2nd_UPS] = 0, 0, 1)
    )
RETURN
IF(Non_Zero_Count = 0, 0, Total_Sum / Non_Zero_Count)

Tested this with sample data, and it works as expected:

  

Hope this helps. Please reach out for further assistance.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.

Best Regards,
Vinay.

[cghanta]

Thank you! @v-veshwara-msft