cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors