Matrix visual Measure Help

[vinodDrinkPak]

hi,

i am using a matrix visual like below, my requiremnt is to have the average comes up at the customer level, right now its adding up the values in the format

when i expant the group if you see there is 2 values in the format, i need to get an average when its rolls up to customer view or when its collapsed?

is there anyway to do it?

i tried create a measure using ChatGPT but it still doesn't gives me the output of customer as Average, rather it adds up the values in the format

Total CEsTest 1 =

VAR IsCustomerFiltered =

    ISCROSSFILTERED('CE Per Pallet'[Customer Name])

VAR IsFormatFiltered =

    ISCROSSFILTERED('CE Per Pallet'[Format])

RETURN

    IF(

        IsCustomerFiltered && NOT IsFormatFiltered,

        AVERAGEX('CE Per Pallet', 'CE Per Pallet'[Total CEs]),  -- Average at Customer level

        SUM('CE Per Pallet'[Total CEs])  -- Sum at Format level

    )

https://drive.google.com/file/d/11xIOy9rfHCpuY0GA6xUeDto4CKKKUYpO/view?usp=sharing

please find the pbix file for testing

[amitchandak · Accepted solution]

@vinodDrinkPak , Assume format is lower level and customer is upper, you can try like

if(isinscope('CE Per Pallet'[Format]), SUM('CE Per Pallet'[Total CEs]) , AVERAGEX(Values('CE Per Pallet'[Format]), calculate(SUM('CE Per Pallet'[Total CEs]))))

Avg of Sum : https://youtu.be/cN8AO3_vmlY?t=22980

[v-zhouwen-msft · Accepted solution]

Hi,@amitchandak，thanks for the quick reply, I will add further.

Hi,@vinodDrinkPak，

Regarding your question, the totals are calculated based on the context and since the DAX expression uses the SUM function, the totals will add up.

I tried to average the values at the 'Format' level, and I don't have any idea about the 'Customer' level, please refer to the following DAX expression for detailed steps.

Total CEsTest 1 = 
IF(ISINSCOPE('CE Per Pallet'[Format]),
IF(SELECTEDVALUE('CE Per Pallet'[EOM]),
 SUM('CE Per Pallet'[Total CEs]) ,
 CALCULATE(SUM('CE Per Pallet'[Total CEs]),ALL('CE Per Pallet'[EOM]))  / CALCULATE(DISTINCTCOUNT('CE Per Pallet'[EOM]),ALL('CE Per Pallet'[EOM]))),
 AVERAGEX(Values('CE Per Pallet'[Format]), calculate(SUM('CE Per Pallet'[Total CEs]))))

Final output :

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@vinodDrinkPak , Assume format is lower level and customer is upper, you can try like

if(isinscope('CE Per Pallet'[Format]), SUM('CE Per Pallet'[Total CEs]) , AVERAGEX(Values('CE Per Pallet'[Format]), calculate(SUM('CE Per Pallet'[Total CEs]))))

Avg of Sum : https://youtu.be/cN8AO3_vmlY?t=22980

[vinodDrinkPak]

@amitchandak 

I tried this and it worked but it doesn't work for the total, total should also be average, both at format and Customer level, i know its a bit tricky and complicated, anything you can help, it will be great

[vinodDrinkPak]

@amitchandak 

i tried several ways i guess, this cannot be achieved?