Different Results Depending on the Drill Down Level

[GreenAnalyst]

Dear PB-Community,

Thank you for your help!

Background: We are a food retail company. In other words, we operate numerous supermarkets.

I am currently working on creating an overview for my colleagues in the purchasing department that will enable them to understand which categories, product groups and/ or products are responsible for the highest/ lowest percentage change in growth compared to the previous period. Therefore, I want to enable them to drill down from the top level (Kategorie = category) down to the product group and product level to see the respective numbers/ KPIs we are concerned with.

The first screenshot shows the top level, i.e. the cateogries. Framed in red is the part of the overview that I am concerned with:

 

1. HS is the trade margin, i.e. gross profit divided by sales.

2. Delta [absolut] is the absolute percentage change in HS/ trade margin compared to the previous period.

3. Rate is the contribution to change/ contribution to growth.

CTC & Mix can be ignored in this case.

Now my problem:

The Rate is correct at the category level. However, when I drill down to product group or product level, I get incorrect values. Example: The Rate (on the produt level) for the two products 146 & 0 should add up to the total value (screenshot 2). But this is not the case.

 

Screenshot 3 shows the measure I use to calculate the rate. To do this, I take the delta of the HS/ trade margin of the current period and the HS/ trade margin of the previous period and multiply this by the current sales (per category, product group or article - depending on where I am in the drill down hierarchy). I then divide this by the total sales for the current period. I think this is where the error lies, i.e. in the CALCULATE formula and the ALLSELECTED.

 

Screenshot 4 shows how the tables in my model are connected.

 

I hope this all makes sense and someone can help me. Thanks alot.

Best,

Green Analyst

[GreenAnalyst · Accepted solution]

It was my mistake. I was able to fix it myself. I now use allselected() with empty brackets. That works perfectly well.

Thank you for your help!

[Kedar_Pande]

@GreenAnalyst ,

Try adjusting the measure to ensure that the context for each level in the hierarchy is maintained correctly.

You can modify the CALCULATE function by using REMOVEFILTERS instead of ALLSELECTED to ensure that you only include the current context for the hierarchy level you're on.

[GreenAnalyst]

Thanks so much for the quick reply!

Unfortunately, this doesn't work. I have tried it before. I changed ALLSELECTED into REMOVEFILTERS. Please see screenshot for the result that I get. I mainly geht 0s now. This doesn't make much sense.

[Kedar_Pande]

Please upload the file on G-drive or onedrive and share the link