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.
Hi All,
I am trying to calculate Inflation in my report, which is basically (Base Rate - Current Rate) * Current Quantity. But this formula is only valid at Material Level, whenever we go to a different level, Material Group or Category, Inflation should be calculted as SUM of all Inflation at Material level.
I am trying the measure below,
Solved! Go to Solution.
@Anonymous
Not quite sure about your data model an what you have in you visual but can you try the following:
Inflation YoY (Calculated) =
SUMX (
VALUES ( RPM[Material] ),
VAR curr_rate = [Rate (Calculated)]
VAR curr_qty = [Qty (Calculated)]
VAR base_rate =
CALCULATE ( [Rate (Calculated)], DATEADD ( 'Calendar'[Date], -1, YEAR ) )
RETURN
( base_rate - curr_rate ) * curr_qty
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
Not quite sure about your data model an what you have in you visual but can you try the following:
Inflation YoY (Calculated) =
SUMX (
VALUES ( RPM[Material] ),
VAR curr_rate = [Rate (Calculated)]
VAR curr_qty = [Qty (Calculated)]
VAR base_rate =
CALCULATE ( [Rate (Calculated)], DATEADD ( 'Calendar'[Date], -1, YEAR ) )
RETURN
( base_rate - curr_rate ) * curr_qty
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi,
I was wrong, your formula is working perfectly. Thanks a lot.
Sourav
Hi,
I think the DAX you shared would be perfect for my Inflation calculation at "Material" level. But when I am trying to get Inflation at Material Group or Category level, it should be the SUM of all Infaltion of all Materials part of that group or category. How would I achieve that in the same measure? That at Material level, it should use the formula (Base Rate - Current Rate)*Current Quantity, but at Group or Category level, it should give SUM of inflation calculated at Material level.
As for my Data Model, it is extremely simple, I just have one Fact table called "RPM", it is connected to just one Date Dimension Table called "Calendar". That's it.
Let me know if you need further details.
Thanks,
Sourav
Hi,
It is hard to visualize this without example data and knowing how your measures work. But basically the wau you can troubleshoot this kind of dilemma is to take the temp table and view it as a calculated table. It is easier to get the correct numbers in a calculated table when you can see the values and when you are satisfied with the logic you can palce your table back into a variable.
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
Proud to be a Super User!
Hi,
I tried visualizing the temp_table in a calculated table, like you said, but I am getting same value for each material in that table. I think the filter context is not working in that calculated table.
And sorry for not being able to share the PBIX file or the data, but I can try to explain what I am trying to achieve,
I have 2 Materials, i and ii, below are the numbers I am getting for them,
Material | Base Rate | Current Rate | Current Qty | Inflation |
i | 65,843.70 | 80,431.74 | 70.71 | (1,031,506.14) |
ii | 35,062.74 | 62,463.33 | 1,357.56 | (37,197,977.42) |
The Inflation value I am getting for these Materials are correct, But when I view the data at Material Group level, I should get (38,229,483.56) as Inflation, which is the aggregate Inflation of i and ii. Instead I am getting (78,961,822.07) for some reason. Not sure why. This is using the Measure I had shared in my original post.
Thanks,
Sourav
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |