- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Need Help!! How to calculate different row items
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Anonymous
The expected output is "vs. Prior wave" number. It is highlighted in the picture.
Thank you
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
06-17-2024 01:42 PM | |||
07-14-2024 03:30 AM | |||
07-16-2024 08:16 AM | |||
04-16-2024 03:12 AM | |||
07-11-2024 08:35 AM |
User | Count |
---|---|
121 | |
75 | |
46 | |
44 | |
35 |
User | Count |
---|---|
180 | |
85 | |
68 | |
47 | |
46 |