Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
MarvellKid
Frequent Visitor

Compare value from a matrix to first row

I have the following matrix table with two different forecasts submitted over the last few months for January 24.

LAG Table.PNG

 

 

 

 

 

 

 

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')

current.PNG

 

 

 

 

 

 

 

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:

Example.PNG

 

I have a sample PBI file available here to download:

Sample PBI report 

 

Thanks in advance

 

Here is the source data:

SKUMonthForecast CreationRegionTypeActualsForecast TypeForecast
30069Jan-24Dec-23EnglandFranchise200Original100
30069Jan-24Dec-23EnglandEquity2000Original1000
30069Jan-24Dec-23IrelandFranchise75Original2000
30069Jan-24Dec-23IrelandEquity750Original2100
40069Jan-24Dec-23EnglandFranchise1000Original500
40069Jan-24Dec-23EnglandEquity1000Original1700
40069Jan-24Dec-23IrelandFranchise2100Original450
40069Jan-24Dec-23IrelandEquity750Original500
50069Jan-24Dec-23EnglandFranchise299Original450
50069Jan-24Dec-23EnglandEquity250Original200
50069Jan-24Dec-23IrelandFranchise600Original450
50069Jan-24Dec-23IrelandEquity750Original900
30069Jan-24Dec-23EnglandFranchise200Updated110
30069Jan-24Dec-23EnglandEquity2000Updated1100
30069Jan-24Dec-23IrelandFranchise75Updated2200
30069Jan-24Dec-23IrelandEquity750Updated2310
40069Jan-24Dec-23EnglandFranchise1000Updated550
40069Jan-24Dec-23EnglandEquity1000Updated1870
40069Jan-24Dec-23IrelandFranchise2100Updated495
40069Jan-24Dec-23IrelandEquity750Updated550
50069Jan-24Dec-23EnglandFranchise299Updated495
50069Jan-24Dec-23EnglandEquity250Updated220
50069Jan-24Dec-23IrelandFranchise600Updated495
50069Jan-24Dec-23IrelandEquity750Updated990
30069Jan-24Nov-23EnglandFranchise200Original50
30069Jan-24Nov-23EnglandEquity2000Original500
30069Jan-24Nov-23IrelandFranchise75Original1000
30069Jan-24Nov-23IrelandEquity750Original1050
40069Jan-24Nov-23EnglandFranchise1000Original250
40069Jan-24Nov-23EnglandEquity1000Original850
40069Jan-24Nov-23IrelandFranchise2100Original225
40069Jan-24Nov-23IrelandEquity750Original250
50069Jan-24Nov-23EnglandFranchise299Original225
50069Jan-24Nov-23EnglandEquity250Original100
50069Jan-24Nov-23IrelandFranchise600Original225
50069Jan-24Nov-23IrelandEquity750Original450
30069Jan-24Nov-23EnglandFranchise200Updated55
30069Jan-24Nov-23EnglandEquity2000Updated550
30069Jan-24Nov-23IrelandFranchise75Updated1100
30069Jan-24Nov-23IrelandEquity750Updated1155
40069Jan-24Nov-23EnglandFranchise1000Updated275
40069Jan-24Nov-23EnglandEquity1000Updated935
40069Jan-24Nov-23IrelandFranchise2100Updated247.5
40069Jan-24Nov-23IrelandEquity750Updated275
50069Jan-24Nov-23EnglandFranchise299Updated247.5
50069Jan-24Nov-23EnglandEquity250Updated110
50069Jan-24Nov-23IrelandFranchise600Updated247.5
50069Jan-24Nov-23IrelandEquity750Updated495
1 ACCEPTED SOLUTION
miTutorials
Super User
Super User

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

View solution in original post

2 REPLIES 2
miTutorials
Super User
Super User

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

Daniel29195
Super User
Super User

@MarvellKid 

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! 🤠

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.