Measure to calculate visual level % of a table visual

[Anonymous]

Hellow Gurus,

I have below requirement. I need previous row as a measure on my table visual.

[Anonymous · Accepted solution]

@mahoneypat ,

thanks for the reply.

I need this calculation to happen at the visual level not in the dataset. what would be the measure.

[mahoneypat · Accepted solution]

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

[mahoneypat]

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

[Anonymous · Accepted solution]

@mahoneypat ,

thanks for the reply.

I need this calculation to happen at the visual level not in the dataset. what would be the measure.