powerbidax

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

How can I add ALL(UPS) to the following measure, so the card value will remain the same:

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)

Solution

  • You need to use CALCULATE to be able to use ALL :

    Measure = 
VAR UPS_Table = 
    CALCULATETABLE('UPS', ALL('UPS'))

VAR Total_Sum = 
    SUMX(UPS_Table, 'UPS'[1st_UPS] + 'UPS'[2nd_UPS])

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

RETURN
    DIVIDE(Total_Sum, Non_Zero_Count, 0)