How to work with an Index and its weights for drill-down?

[AlejandroPCar]

Hi!

I'm working with Consumer Price Index in Power BI (cool) but I tried and I dont know how to this CPI works well for sum or do drill -down. I have for the CPI its structure in the following levels: Group, Sub-Group, Class and Basic Expenditure. There is a dax function for do the lower index level sums the next level and so on?  I dont know how to work with index data.

Here is my pbix file if it will be necesary.

https://1drv.ms/u/s!AtTnrgPUQzQCg06sZ_Ul-uiYEcRF

Thanks a lot for read me!

[v-yuezhe-msft]

@AlejandroPCar,

Do you want to create a chart with drill down feature? If so, you can create a clustered column chart as follows, by clicking the double-arrow button, you are able to drill down to next level of the hierarchy and the chart will show you Index data for this level. For more details, please review this modified PBIX file.



Regards,

[AlejandroPCar]

Hi!

Ohh.. sorry it was my mistake... sorry sorry this is not my issue, its dificult for me make me understand, the real problem is the following:

I have index and its weitghts. Every class is made by some basic expenditures like that ie.

Sugar index is 0,5% of total CPI index

Rice is 0,7%

Milk is 1,5%

and those 3 make the class of amm "Kitchen class" and its weight of total is 2,7% of total CPI (for example), but like those weights are of total CPI, the real weitghs for this particular "Kitchen class" will be:

Sugar 19%

Rice 26%

Milk 55%

So when I do:

Sugar 100,56 * 19% = 19,10

Rice 150,10 * 26% = 30,02

Milk 98,34 * 55%  = 54,08 and then I sum these values the result 103,2 is the index for "Kitchen class" and If I multiply this index for it weitght (2,7%) and I do the same with the other class I have the total CPI index.

This thing applies to the next levels in the same way. This is my problem, I need multiply the index by the weigths and at the end, the final value that will be in the final  level it will be sum 100% of total CPI and I dont know how to do that. And it will be work when I do drill-down.

Woh, I know... this must have been what I wrote first... sorry sorry

Sorry again!

[v-yuezhe-msft]

@AlejandroPCar,

I am not quite clear about your requirement. Could you please post expected result(calculated based on the data in your PBIX file) in table?

Regards,

[AlejandroPCar]

Hi!

The total CPI should be something like 137,77 (the sum of all basic expenditures index multiplied by its weights) and the idea is if then I go the next level (class) and I sum all those the result will be the same and so on for the next levels.

So, the first class "Cereales" the result will be 115,00 (using the method I posted previously) the second class "Productos de Panaderia" will be 136,36 and these 2 form the first subgroup "Cereales y Productos de Panaderia" and the result will be 123,28 and so son for the next level.

Here is an excel where I calculate all the levels for the first group

 I hope it helps.

https://1drv.ms/x/s!AtTnrgPUQzQCg08em55jnh7X7rVp

Thanks again!