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!
