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'm a couple of weeks into using Power BI now. Great tool, a little overwhelming at first.
I'm trying to work out how to get the % difference between ££ values per row, per column.
Here's the data I'm working on, so I want to work out the difference from one month to the next, per column (so each column has it's own % difference to show)
Date | Div1 | Div2 | Div3 | Div4 | Div5 | Div6 | Div7 | Div8 |
01/01/2020 00:00 | £22,440.00 | £5,876.00 | £36,886.00 | £6,845.00 | £845.00 | £43,996.00 | £2,239.00 | £711.00 |
01/12/2019 00:00 | £21,825.00 | £5,873.00 | £35,815.00 | £6,844.00 | £845.00 | £42,171.00 | £2,239.00 | £711.00 |
01/11/2019 00:00 | £22,132.00 | £5,872.00 | £35,256.00 | £6,844.00 | £845.00 | £40,837.00 | £1,989.00 | £709.00 |
01/10/2019 00:00 | £23,788.00 | £4,126.00 | £33,640.00 | £6,860.00 | £845.00 | £38,627.00 | £1,989.00 | £709.00 |
01/09/2019 00:00 | £24,050.00 | £3,453.00 | £32,935.00 | £6,846.00 | £845.00 | £36,572.00 | £1,989.00 | £709.00 |
01/07/2019 00:00 | £24,307.00 | £3,453.00 | £30,738.00 | £6,843.00 | £845.00 | £35,669.00 | £1,989.00 | £707.00 |
01/06/2019 00:00 | £22,333.00 | £3,264.00 | £28,785.00 | £6,845.00 | £845.00 | £34,686.00 | £1,988.00 | £707.00 |
Any ideas how I might achieve that please?
Thanks.
Solved! Go to Solution.
You can do both depending on the model, but typically that's a measure, a time intelligence one.
Do you have a date dimension?
If not, please create it (look it up on the web, there are plenty of resources if you look for "how to create a date dim in powerbi")
Once you have it created, you need a measure that basically does your value calcuation
Amount=SUMX(Table;Table[amount])
which basically calculate your amount (your value in the cell at the interesection of month and div)
Then do a previous month calculation
AmountPreviousMonth=CALCULATE([Amount];PREVIOUSMONTH(DateTable[DateKey])
This will calculate the same amount but on previous month.
Then do the percentage in a new measure
p.s. I write pseudocode, so correct syntax if needed.
Date | Div1 | Diff Div1 | Div2 | Diff Div2 |
01/01/2020 00:00 | £22,440.00 | -- | £5,876.00 | |
01/12/2019 00:00 | £21,825.00 | -2.8% | £5,873.00 | -0% |
01/11/2019 00:00 | £22,132.00 | +1.4% | £5,872.00 | -0% |
01/10/2019 00:00 | £23,788.00 | ... | £4,126.00 | .. |
01/09/2019 00:00 | £24,050.00 | ... | £3,453.00 | .. |
01/07/2019 00:00 | £24,307.00 | ... | £3,453.00 | ... |
01/06/2019 00:00 | £22,333.00 | ... | £3,264.00 | .. |
Something like this?
Hi, yes that's the result I'm after. Should I do that as a calculated column, or a measure? (if so, how please?)
Thanks.
You can do both depending on the model, but typically that's a measure, a time intelligence one.
Do you have a date dimension?
If not, please create it (look it up on the web, there are plenty of resources if you look for "how to create a date dim in powerbi")
Once you have it created, you need a measure that basically does your value calcuation
Amount=SUMX(Table;Table[amount])
which basically calculate your amount (your value in the cell at the interesection of month and div)
Then do a previous month calculation
AmountPreviousMonth=CALCULATE([Amount];PREVIOUSMONTH(DateTable[DateKey])
This will calculate the same amount but on previous month.
Then do the percentage in a new measure
p.s. I write pseudocode, so correct syntax if needed.
Perfect, thanks!
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 |
---|---|
105 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |