DAX - Performance change

[Anonymous]

I have data that looks like this:

Name	Performance	Year
John	High	2020
John	Solid	2019
Andrew	Exceptional	2020
Andrew	High	2019
Mary	Below	2020
Mary	Below	2019

I need to create 3 cards: % of these people that increased performance, % of these people that kept same performance and % of these people that decreased their performance.

How can I do this through DAX?

ps. I can't create calculated columns, or add any columns because it's a restricted database

[Fowmy]

@Anonymous 

Create a table for Performance Ranking

Link Performance and Ranking

Add the Measures ( You change the sign ">" to "<" for Decreased % and "=" for Same %

Increased % = 
VAR __R = 
SUMX(
    ALLEXCEPT(Table6,Table6[Name]),
        IF( 
            Table6[Year] = 2020 && 
            RELATED('Performance Table'[RankNo]) > minX(FILTER( Table6 , Table6[Year]= 2019), RELATED('Performance Table'[RankNo])),
            1,
            0
        )
    )

VAR __C = COUNTROWS(ALL(Table6[Name]))

RETURN

DIVIDE(
    __R,
    __C
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

@Fowmy I can't add any columns, it's a restricted database. Is there a way to do it all through DAX?

[Fowmy]

@Anonymous 

Add the following Measure, no need to create a table or add columns.
This Measure is for Increased, for Decreased, and Same only adjust the 3rd Row from last to [Rank] < 0 and [Rank] = 0 respectively.

Increased% M = 
VAR T1 = { ( "Below", 1 ), ( "Solid", 2 ), ( "High", 3 ), ( "Exceptional", 4 ) }
VAR T2 =
    FILTER ( GENERATE ( table6, T1 ), Table6[Performance] = [Value1] )
VAR T3 = 

ADDCOLUMNS(
    FILTER (
        GENERATE (
            table6,
            SELECTCOLUMNS (
                T2,
                "xName", table6[Name],
                "xPerformance", table6[Performance],
                "xYear", table6[Year]
            )
        ),
        Table6[Name] = [xName]
            && Table6[Year] = 2020
            && [xYear] = 2019
    ),
    "Rank", MAXX(FILTER(T1,Table6[Performance] = [Value1]),[Value2]) -  MAXX(FILTER(T1,[xPerformance] = [Value1]),[Value2])
)

RETURN
DIVIDE(
    COUNTROWS(FILTER(T3,[Rank] > 0)),
    COUNTROWS(ALL(Table6[Name]))
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

@Fowmy Performance, Name and Date are actually in 3 different tables that are related. Can you adjust the measure taking that into consideration, please

[Fowmy]

@Anonymous 

The measure is based on a single table, if all those columns come from different tables, 

The best approach will be to request your data source owner to provide you a table in the format you shared. 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn