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.
speed difference
52 0
63 11
76 13
61 5
How do I create a difference column in power bi like the simple calculation in excel =A1-A2 ?
Solved! Go to Solution.
Yes, measures are often the best solution, but there is a way to do it as a column as well: http://excel-inside.pro/blog/2015/11/05/absolute-and-relative-references-in-power-query/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @edgarjb-68 ,
please check this method on how to reference a different row in PQ: https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-po...
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
So I am trying to do a similar thing. I have a flat SQL table with insurance premium values by year, region program and contract and want to be able to compare the premiums year on year aggregated by region and program... Any ideas anyone?
Thanks
You could this by creating measures, which you can then reference.
As per your example the following:
Speed Total = sum('TableName'[Speed]) DifferenceTotal = sum('TableName'[Difference]) Difference = [Speed Total] - [Difference Total]
The final measure would then show you the difference. If you wanted to see it by other columns, you would then just drag them into your table.
I am not sure if I am reading your solution correctly.
I get this error: A circular dependency was detected: TableName[Difference].
So Difference is a new column with the result of row1 - row2 in the speed coulumn and so on.
Thank you.
Hi @andysross
Please ensure that you create them as Calculated Measures and NOT calculated columns. That is why you are getting the error as described below.
Yes, measures are often the best solution, but there is a way to do it as a column as well: http://excel-inside.pro/blog/2015/11/05/absolute-and-relative-references-in-power-query/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I ended up using this method. Thankyou
Difference = var A = CALCULATE( MAX('table'[id]))-1 return If('table'[id]=0,'table'[speed], 'table'[speed] - CALCULATE(SUM('table'[speed]),FILTER('table','table'[id] = A)))
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |