cancel
Showing results for
Did you mean:

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

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

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
)

please find the pbix file for testing

2 ACCEPTED SOLUTIONS
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

Community Support

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.

4 REPLIES 4
Community Support

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.

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

Helper II

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

Helper II

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors