Change calculation based on hierarchy level and correct totals

[f1254lipm]

Hi,

I have a problem with hierarchy handling in matrix. I need to change the measure used at the lowest level of hierarchy, if id is equal to specified value and then have correct values at higher hierarchy levels. For example if the lowest hierarchy level id is equal to 1111 I want to multiply the value on that row by 0.1 and then this modified value at the lowest level should be summed at higher levels of hierarchy. Now the value is modified at the lowest level but on higher level the old non modified value is summed and the total is incorrect. Is there a way to do this?

Thanks

[f1254lipm]

Hi @123abc, thank you so much for your help and time.

[123abc · Accepted solution]

In Power BI, you can achieve this by using a combination of DAX measures and calculated columns to handle hierarchy calculations and apply specific transformations at different levels of the hierarchy. You'll need to create a calculated column for the hierarchy level and then use DAX measures to perform the required calculations.

Here are the general steps to implement this in Power BI:

1. Define Hierarchy Levels: First, you need to establish the hierarchy levels in your data model. For example, you may have a hierarchy that consists of multiple levels like Region, Country, City, etc.

2. Create a Calculated Column for Hierarchy Level: Create a calculated column that assigns a level number to each row in your data based on its position in the hierarchy. This will help you identify the lowest level where the value should be modified. For example:

HierarchyLevel = SWITCH(TRUE(),
[City ID] = 1111, 1,
[Country ID] = 1111, 2,
[Region ID] = 1111, 3,
0
)

1. This code assigns a level number (1, 2, 3, etc.) based on the ID condition.

2. Create Measures for Modification and Aggregation: Next, create DAX measures for modifying values at the lowest level and aggregating values at higher levels.

   • Modify the Value at the Lowest Level:

ModifiedValue = IF([HierarchyLevel] = 1, [OriginalValue] * 0.1, [OriginalValue])

1. • This measure will multiply the value by 0.1 if it's at the lowest level.

   • Aggregate Values at Higher Levels: You can use standard aggregation functions like SUM, AVERAGE, or any other as per your requirements.

2. Build Your Visuals: Use the ModifiedValue measure for visualizations where you want to display the value at the lowest level with the modification and the Aggregate measures for higher-level visuals.

By following these steps, you will be able to modify the values at the lowest level of the hierarchy and correctly aggregate them at higher levels. The calculated column "HierarchyLevel" helps identify the level, and DAX measures ensure the correct calculations are performed.

Remember to replace [City ID], [Country ID], [Region ID], and [OriginalValue] with the actual column names from your dataset.

Power BI's ability to create calculated columns and measures in DAX provides a flexible way to handle hierarchy calculations and apply specific transformations based on your business requirements.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.