Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
vinodDrinkPak
Helper II
Helper II

Matrix visual Measure Help

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

 

vinodDrinkPak_1-1712336343439.png

 

 

vinodDrinkPak_2-1712336396062.png

 

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

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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

View solution in original post

v-zhouwen-msft
Community Support
Community Support

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.

vzhouwenmsft_0-1712561415572.png

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 :

 

vzhouwenmsft_1-1712561588435.png

 


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.

 

View solution in original post

4 REPLIES 4
v-zhouwen-msft
Community Support
Community Support

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.

vzhouwenmsft_0-1712561415572.png

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 :

 

vzhouwenmsft_1-1712561588435.png

 


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
Super User
Super User

@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

@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_0-1712373239285.png

 

 

 

@amitchandak 

 

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

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.