Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I have the following matrix table with two different forecasts submitted over the last few months for January 24.
What I want to be able to do, is return the variance between M-01 and all of the preceeding rows.
Ive been able to get it to work when Im not using columns across tables.
This is what I've been able to get to work so far.
See 'Measure2' showing the sum of M-01 of both original and updated.
('Measure' is then '.m Forecast' - 'Measure2')
As an additional, I wanted to create a new matrix table, that would compare against the Actuals Value for M-01 to the forecast values for M-##.
Example below of what I would like to show in my final output table:
I have a sample PBI file available here to download:
Thanks in advance
Here is the source data:
SKU | Month | Forecast Creation | Region | Type | Actuals | Forecast Type | Forecast |
30069 | Jan-24 | Dec-23 | England | Franchise | 200 | Original | 100 |
30069 | Jan-24 | Dec-23 | England | Equity | 2000 | Original | 1000 |
30069 | Jan-24 | Dec-23 | Ireland | Franchise | 75 | Original | 2000 |
30069 | Jan-24 | Dec-23 | Ireland | Equity | 750 | Original | 2100 |
40069 | Jan-24 | Dec-23 | England | Franchise | 1000 | Original | 500 |
40069 | Jan-24 | Dec-23 | England | Equity | 1000 | Original | 1700 |
40069 | Jan-24 | Dec-23 | Ireland | Franchise | 2100 | Original | 450 |
40069 | Jan-24 | Dec-23 | Ireland | Equity | 750 | Original | 500 |
50069 | Jan-24 | Dec-23 | England | Franchise | 299 | Original | 450 |
50069 | Jan-24 | Dec-23 | England | Equity | 250 | Original | 200 |
50069 | Jan-24 | Dec-23 | Ireland | Franchise | 600 | Original | 450 |
50069 | Jan-24 | Dec-23 | Ireland | Equity | 750 | Original | 900 |
30069 | Jan-24 | Dec-23 | England | Franchise | 200 | Updated | 110 |
30069 | Jan-24 | Dec-23 | England | Equity | 2000 | Updated | 1100 |
30069 | Jan-24 | Dec-23 | Ireland | Franchise | 75 | Updated | 2200 |
30069 | Jan-24 | Dec-23 | Ireland | Equity | 750 | Updated | 2310 |
40069 | Jan-24 | Dec-23 | England | Franchise | 1000 | Updated | 550 |
40069 | Jan-24 | Dec-23 | England | Equity | 1000 | Updated | 1870 |
40069 | Jan-24 | Dec-23 | Ireland | Franchise | 2100 | Updated | 495 |
40069 | Jan-24 | Dec-23 | Ireland | Equity | 750 | Updated | 550 |
50069 | Jan-24 | Dec-23 | England | Franchise | 299 | Updated | 495 |
50069 | Jan-24 | Dec-23 | England | Equity | 250 | Updated | 220 |
50069 | Jan-24 | Dec-23 | Ireland | Franchise | 600 | Updated | 495 |
50069 | Jan-24 | Dec-23 | Ireland | Equity | 750 | Updated | 990 |
30069 | Jan-24 | Nov-23 | England | Franchise | 200 | Original | 50 |
30069 | Jan-24 | Nov-23 | England | Equity | 2000 | Original | 500 |
30069 | Jan-24 | Nov-23 | Ireland | Franchise | 75 | Original | 1000 |
30069 | Jan-24 | Nov-23 | Ireland | Equity | 750 | Original | 1050 |
40069 | Jan-24 | Nov-23 | England | Franchise | 1000 | Original | 250 |
40069 | Jan-24 | Nov-23 | England | Equity | 1000 | Original | 850 |
40069 | Jan-24 | Nov-23 | Ireland | Franchise | 2100 | Original | 225 |
40069 | Jan-24 | Nov-23 | Ireland | Equity | 750 | Original | 250 |
50069 | Jan-24 | Nov-23 | England | Franchise | 299 | Original | 225 |
50069 | Jan-24 | Nov-23 | England | Equity | 250 | Original | 100 |
50069 | Jan-24 | Nov-23 | Ireland | Franchise | 600 | Original | 225 |
50069 | Jan-24 | Nov-23 | Ireland | Equity | 750 | Original | 450 |
30069 | Jan-24 | Nov-23 | England | Franchise | 200 | Updated | 55 |
30069 | Jan-24 | Nov-23 | England | Equity | 2000 | Updated | 550 |
30069 | Jan-24 | Nov-23 | Ireland | Franchise | 75 | Updated | 1100 |
30069 | Jan-24 | Nov-23 | Ireland | Equity | 750 | Updated | 1155 |
40069 | Jan-24 | Nov-23 | England | Franchise | 1000 | Updated | 275 |
40069 | Jan-24 | Nov-23 | England | Equity | 1000 | Updated | 935 |
40069 | Jan-24 | Nov-23 | Ireland | Franchise | 2100 | Updated | 247.5 |
40069 | Jan-24 | Nov-23 | Ireland | Equity | 750 | Updated | 275 |
50069 | Jan-24 | Nov-23 | England | Franchise | 299 | Updated | 247.5 |
50069 | Jan-24 | Nov-23 | England | Equity | 250 | Updated | 110 |
50069 | Jan-24 | Nov-23 | Ireland | Franchise | 600 | Updated | 247.5 |
50069 | Jan-24 | Nov-23 | Ireland | Equity | 750 | Updated | 495 |
Solved! Go to Solution.
Have you tried the new Visual Calculation feature ? you can refer to the rows just like the way you refer in excel and create calculations. Below tutorial will help you.
🚀 NEW FEATURE : Magic of Visual Calculations in Power BI | MiTutorials - YouTube
Regards
Ismail
Have you tried the new Visual Calculation feature ? you can refer to the rows just like the way you refer in excel and create calculations. Below tutorial will help you.
🚀 NEW FEATURE : Magic of Visual Calculations in Power BI | MiTutorials - YouTube
Regards
Ismail
@Anonymous
use the window function ( index ) ,
the index will get the first value of the first row
this is an exmaple of how to use it .
visual table :
month sales difference between current row and first row (to calculate )
m1 1 1 - 1
m2 2 2 - 1
m3 3 3- 1
m4 4 4-1
measure=
var current_row = [sales]
var first_row =
calculate(
index(1,
allselected(tbl_name[month]),
orderby(tbl_name[month] , asc )
),
[sales]
)
return current_row = first_row
let me know if this helps .
nb: you also can have a partitionby paramter for the index, if example, you want to get the differene between each month per a group . so the column_name on wchih you want to group your data on, will be added with the partitionby paramter of the index function
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
124 | |
77 | |
75 | |
58 | |
49 |
User | Count |
---|---|
166 | |
84 | |
68 | |
67 | |
57 |