cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

DAX - Performance change

I have data that looks like this:

 

NamePerformanceYear
JohnHigh2020
JohnSolid2019
AndrewExceptional2020
AndrewHigh2019
MaryBelow2020
MaryBelow2019

 

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

5 REPLIES 5
Highlighted
Community Champion
Community Champion

@belaberger 

Create a table for Performance Ranking

Fowmy_0-1600984848114.png


Link Performance and Ranking

Fowmy_1-1600984908665.png

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

 

 

Highlighted

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

Highlighted
Community Champion
Community Champion

@belaberger 

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

Highlighted

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

Highlighted
Community Champion
Community Champion

@belaberger 


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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors