Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hellow Gurus,
I have below requirement. I need previous row as a measure on my table visual.
Solved! Go to Solution.
thanks for the reply.
I need this calculation to happen at the visual level not in the dataset. what would be the measure.
Here is a measure that should work:
Prev Amt =
VAR __thissubtask =
MIN ( Table[SubTask ID] )
VAR __thisSUM =
SUM ( Table[Amount] )
VAR __prevamt =
CALCULATE (
SUM ( Table[Amount] ),
ALLEXCEPT ( Table, Table[Program ID], Table[Task ID] ),
Table[SubTask ID] = __thissubtask - 1
)
RETURN
IF ( ISBLANK ( __prevamt ), __thisSUM, __prevamt )
For the ratio, just use DIVIDE with your orignal measure and this measure.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Here is a measure that should work:
Prev Amt =
VAR __thissubtask =
MIN ( Table[SubTask ID] )
VAR __thisSUM =
SUM ( Table[Amount] )
VAR __prevamt =
CALCULATE (
SUM ( Table[Amount] ),
ALLEXCEPT ( Table, Table[Program ID], Table[Task ID] ),
Table[SubTask ID] = __thissubtask - 1
)
RETURN
IF ( ISBLANK ( __prevamt ), __thisSUM, __prevamt )
For the ratio, just use DIVIDE with your orignal measure and this measure.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
If this does not need to be dynamic, you can do it in the query editor by adding an Index column starting at zero and then adding a custom column with a formulat like this
= try #"Added Index"[ColumnWIthDesiredValue]{[Index]-1} otherwise null
Where #"Added Index" is the name of the previous step and your desired column name goes in the [ ]. The try otherwise part is just to avoid an error on the very first value (which has no previous one). Once you have the column, you can then write a simple measure to get the previous result into your visual (same aggregation as current result, and just replace column name).
If you do need it to be dynamic (e.g., respond to slicers, work with different colums), I think you'll need to provide more details on needed functionality and context.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
thanks for the reply.
I need this calculation to happen at the visual level not in the dataset. what would be the measure.
User | Count |
---|---|
100 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |