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)
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)
Share asked Mar 31 at 2:22 cghantacghanta 373 bronze badges New contributor cghanta is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.1 Answer
Reset to default 1You 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)