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.
hi,
I am trying to Calculate the difference between waves, but I am running into some issues. These waves are in rows and a text format ( not date ). I also want to select a specific wave and automatically show the "vs. prior wave" number. Thank you so much!!
Solved! Go to Solution.
Hi @Perezjav
I build a table like yours to have a test.
Firstly, you need to transform your table by unpivot column like pranit828 replied before. (Select three wave columns)
Result:
Then build a Slicer Table.
Slicer = VALUES('Table'[Wave])
Add an Index column in Table.
Index = RANKX(FILTER('Table','Table'[Category]=EARLIER('Table'[Category])),'Table'[Wave],,ASC)
Build a measure and build a matrix visual to achieve your goal.
vs Prior Wave =
VAR _select =
SELECTEDVALUE ( Slicer[Wave] )
VAR _selindex =
CALCULATE (
SUM ( 'Table'[Index] ),
FILTER (
'Table',
'Table'[Category] = MAX ( 'Table'[Category] )
&& 'Table'[Wave] = _select
)
)
VAR _current =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Category] = MAX ( 'Table'[Category] )
&& 'Table'[Index] = _selindex
)
)
VAR _prior =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Category] = MAX ( 'Table'[Category] )
&& 'Table'[Index] = _selindex - 1
)
)
RETURN
_current - _prior
Result is as below.
Default:
Select Wave2/Wave3 in Slicer:
You can download the pbix file from this link: Need Help!! How to calculate different row items
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
The expected output is "vs. Prior wave" number. It is highlighted in the picture.
Thank you
Hi @Perezjav
I build a table like yours to have a test.
Firstly, you need to transform your table by unpivot column like pranit828 replied before. (Select three wave columns)
Result:
Then build a Slicer Table.
Slicer = VALUES('Table'[Wave])
Add an Index column in Table.
Index = RANKX(FILTER('Table','Table'[Category]=EARLIER('Table'[Category])),'Table'[Wave],,ASC)
Build a measure and build a matrix visual to achieve your goal.
vs Prior Wave =
VAR _select =
SELECTEDVALUE ( Slicer[Wave] )
VAR _selindex =
CALCULATE (
SUM ( 'Table'[Index] ),
FILTER (
'Table',
'Table'[Category] = MAX ( 'Table'[Category] )
&& 'Table'[Wave] = _select
)
)
VAR _current =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Category] = MAX ( 'Table'[Category] )
&& 'Table'[Index] = _selindex
)
)
VAR _prior =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Category] = MAX ( 'Table'[Category] )
&& 'Table'[Index] = _selindex - 1
)
)
RETURN
_current - _prior
Result is as below.
Default:
Select Wave2/Wave3 in Slicer:
You can download the pbix file from this link: Need Help!! How to calculate different row items
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Perezjav
To start with unpivot all three wave column and then use the earlier function to get the previous wave data.
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
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 |
---|---|
126 | |
78 | |
77 | |
60 | |
52 |
User | Count |
---|---|
164 | |
84 | |
68 | |
68 | |
58 |