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,
Im working in Power Query in Excel and I need to find the percent difference between a target and a result in a previous period. I have several half yearly periods in my database, eg 2018B, 2019A, 2019B, 2020A etc.
At present, my database has column headers and data representing:
unit | period | result | target |
widgetA | 2019A | 4 | 5 |
widgetB | 2019B | null | 6 |
So, I need to calculate: target for 2019B / result for 2019A, and then format as a percent. (And calculate this for all the periods). Answer for above would be 6/4 = 150%
Thanks for your help, Rogerbij
Solved! Go to Solution.
you are not gonna use it in PowerPivot right? if yes then I think it's better to calculate it there with DAX, as you won't have the classic ratio&aggregation issues
as for the M solution - I suggest putting previous result in the same row using @ImkeF technique described here
https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-po...
then you just divide with simple calculated column and remove the one representing previous row
you are not gonna use it in PowerPivot right? if yes then I think it's better to calculate it there with DAX, as you won't have the classic ratio&aggregation issues
as for the M solution - I suggest putting previous result in the same row using @ImkeF technique described here
https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-po...
then you just divide with simple calculated column and remove the one representing previous row
Hi @Stachu ,
Thanks for this, and an interesting read. I think since I only have limited use for this Im going to cheat and just calculate a few summary tables and by-pass this altogether.
Best, Roger
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.