Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
xanderdiederen
Frequent Visitor

Calculating measures based on different hierarchy levels

Hi All,

 

I'm pretty new to the world of PowerBI and have a problem with a certain measure that i'm trying to create. 

I have several consumer goods that are classified into different level of categories. 

 

E.g. I have product 1 which is Ice Tea that is part of Tea, which is part of Beverages which is part of Foods (Product 1 | Category level 1 | Category level 2 | Category level 3). There are several products with different category classifications on the respective levels. 

 

Say I have data for 2 years for several products. I have 'volumes' (number of pieces sold) and 'cost' (in euro) for each product, where 'cost per piece' is calculated as Cost / Volumes. Furthermore, say I want to calculate a measure that does 'volumes (number of products sold) * cost per piece of last year'.

 

I was wondering if there is a way to make sure this measure always calculates on the product 1 level, so in case i filter for a certain category level 2.

 

E.g. with Ice Tea:

if I select filter 'Foods', it calculates total cost for 'foods' and total volumes for 'foods'. Respectively, the 'volumes (number of products sold) * cost per piece of last year' measure gets calculated on the same way. 

 

What I actually need it to do, is calculate the 'volumes (number of products sold) * cost per piece of last year' for each product 1 level and in case i select Foods, sum the individual product 1 calculations of the different products that contain the 'foods' category level 3 classification. 

 

I tried the following:

 

Deflated YTD = SUMX(ADDCOLUMNS(SUMMARIZE(CALC,CALC[L11 BFPP (FINAL)]),"Column Defl YTD",TOTALYTD(IF(OR([Vol 2018 YTD]<=0,[Vol 2019 YTD]<=0),[Pnl 2019 YTD],[CpT 2018 YTD]*[Vol 2019 YTD]),'Time'[Date],"12/31")),[Column Defl YTD])
 
where
Deflated YTD = volumes (number of products sold) * cost per piece of last year
CALC = the table that contains the volumes and cost of x number of product 1's for different months, over 2 years
[L11 BFPP (FINAL)] = the product 1 level (so columns with all product 1's)
[Vol 2018 YTD] = Volumes on a year to date base of 2018 (so sum of jan - whatever month you select)
[Vol 2019 YTD] = Volumes on a year to date base of 2019 (so sum of jan - whatever month you select)
[Pnl 2019 YTD] = Cost in euro on a year to date base of 2019 (so sum of jan - whatever month you select)
[CpT 2018 YTD] = cost in 2018 / volumes of 2018 (again on a year to date basis). 
 
Couple of notes:
- The Year to Date part is because it needs to be calculated on a YTD basis mathematically
- the 'IF(OR([Vol 2018 YTD]<=0,[Vol 2019 YTD]<=0),[Pnl 2019 YTD]' part is because in case volumes were zero for a product in prior year, the deflated needs to be equal to the amount of cost in 2019, instead of doing CPT of 2018 * volumes of 2019. 
 
Unfortunately the above way is not entirely working because of the following: say a certain product has volumes and cost in Jan/Feb/March in 2019 but no volumes and cost anymore in April 2019. If I select April month, YTD April should give me Jan+Feb+March+April. However, due to the SUMX(ADDCOLUMNS) solution as described above in combination with the F(OR([Vol 2018 YTD]<=0,[Vol 2019 YTD]<=0),[Pnl 2019 YTD] it is basically zero'ing out the Jan/Feb/Mar part as well (i'm guessing this is what is happening, because this is what i'm seeing in the numbers). 
 
Was therefore wondering if there is any other way I can achieve the above result, or if there is a fix for the above problem I tried to describe. I'm understanding it is super abstract but as of now, I didn't know a different way to describe my problem.
 
Thanks in advance!
 
 

 

2 REPLIES 2
TeigeGao
Solution Sage
Solution Sage

Hi @xanderdiederen ,

Could you please share the sample data and expected result to explain your requirement? Actually, we can create multiple separate tables for Category levels, these tables can be used for slicer, then use selectedvalue to judge which value is selected.

Best Regards,

Teige

EDIT: updated the link to the file, I have a separate mapping for 'time' that i now also included in the uploaded file.

 

 

 Hi @TeigeGao 

 

Thanks for your reply! i've upload a data sample PBI file in the following link

 

What you can see there as well is that for certain data rows (so certain months) in the raw data, there is no more 'cost' data for a certain product type. 

E.g. say there is 'cost' data for the months Jan - Apr but not for May and June but there is 'volume' data for the months Jan - June. The measure i'm trying to calculate (Deflated YTD) then gets zero'd out completely eventhough it it should be recalculated with the additional volumes on a year to date base.

 

In case of unclarities please let me know!

 

Thanks again!

 

 

 

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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