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

New Member

## DAX Measure weighted sum on multiple hierarchy levels

Hi there,

I have the following problem, which I hope you can help me with.

Final achievment should be to have a weighted price development ratio over products, product groups and customers. I have a matrix visual including that hierarchy like in the simplified Excel sample below (in my real model btw I have 6 hierarchy levels). On single product level I have a price development ratio (column D) which should be weighted with sales percentage in column C. What grinds my gears is to add that up for the upper hierarchy levels.

On product group level it is the sum of the weighted product ratios but on customer level it has to be the sum of the weighted product group ratios.

My current approach is to calculate the weighted ratio on each level and then using that measure for the next hierarchy level as a basis but for my current data model with around half a million rows this iteration is way to heavy. Formula for product group level ratio would look like:

Weighted Product Group Ratio = IF(ISINSCOPE('Table'[Product Group),SUMX(VALUES('Table'[Product),[Price Development Ratio]*[Sales %]))

Is there any measure to calculate this in a less time consuming way?

If you need additional info let me know. Hope my requirement is somehow understandbable 😄

Any help is much appreciated, cheers!

Super User

looks like you are on the right track. Please quantify "time consuming".

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.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors