Solved: Re: calculate % difference from previous quarter

Marshmallow · ‎11-03-2024

Hi, I have issue trying to get the formula right to get the percentage of difference from previous quarter. Below is my data set (sample that has been cropped off but total LOW are 44 in Jun 24, 67 in Mar24, 35 in Dec 23, 59 in Sep 23 and 47 in Jun 23.

Rating	Period_1
LOW	Mar 24
LOW	Mar 24
LOW	Mar 24
LOW	Jun 23
LOW	Mar 24
LOW	Jun 23
LOW	Sep 23
LOW	Dec 23
LOW	Mar 24
LOW	Jun 24
LOW	Jun 23
LOW	Sep 23
LOW	Dec 23
LOW	Mar 24
LOW	Jun 23
LOW	Sep 23
LOW	Dec 23
LOW	Mar 24
LOW	Jun 24
LOW	Jun 23
LOW	Jun 23
LOW	Sep 23
LOW	Dec 23
LOW	Jun 23
LOW	Sep 23
LOW	Mar 24
LOW	Jun 23
LOW	Sep 23
LOW	Mar 24
LOW	Jun 24
LOW	Sep 23

MOD	Jun 23
MOD	Sep 23
MOD	Dec 23
MOD	Jun 24
MOD	Jun 23
MOD	Sep 23
MOD	Dec 23
MOD	Mar 24
MOD	Jun 24
MOD	Jun 23
MOD	Sep 23
MOD	Dec 23
MOD	Mar 24
MOD	Jun 24
MOD	Jun 23
MOD	Sep 23
MOD	Dec 23
MOD	Mar 24
MOD	Jun 24
MOD	Jun 23
MOD	Sep 23
MOD	Dec 23
MOD	Mar 24
MOD	Jun 24
MOD	Jun 23
MOD	Sep 23
MOD	Dec 23
MOD	Jun 24
MOD	Jun 23
MOD	Sep 23
MOD	Dec 23
MOD	Mar 24
MOD	Jun 24
MOD	Jun 23
MOD	Sep 23
MOD	Dec 23

In Jun24, there were 44 LOW; In Mar24 there were 67 LOW and so on and so forth. I also have a filter on the dashboard that says:

'ALL period, Jun 24, Mar 24, Dec 23, Sep 23, and Jun 23'

I need help with:

1. If 'ALL period is selected' then it will display the same narrative as to when Jun 24 is selected - which is: "In Jun 24, there were 44 low performers (34% reduction compared to Mar 24); 17 moderate performers (an increase of 23% compared to Mar 24)

2. If previous quarter selected on the filter, then show the narrative for previous quarter. For instance, if Mar 24 selected then it will display this narrative: "In Mar 24, there were xx low performers (xx% reduction compared to Dec 23); xx moderate performers (an increase (or reduction) compared to Dec 23.

3. If Jun 23 selected then says: "Insufficienct data"

Is this possible?

Thank you.

Sahir_Maharaj · ‎11-03-2024

Hello @Marshmallow,

Can you please try this approach:

1. create a measure that counts the number of "LOW" performers

LowCount = 
CALCULATE(
    COUNTROWS('YourTable'),
    'YourTable'[Rating] = "LOW"
)

2. create a measure for counting "MOD" (Moderate) performers

ModerateCount = 
CALCULATE(
    COUNTROWS('YourTable'),
    'YourTable'[Rating] = "MOD"
)

3. for each rating, create a measure to get the count of the previous quarter

LowCount_PrevQuarter = 
CALCULATE(
    [LowCount],
    PREVIOUSQUARTER('YourTable'[Period_1])
)

ModerateCount_PrevQuarter = 
CALCULATE(
    [ModerateCount],
    PREVIOUSQUARTER('YourTable'[Period_1])
)

4. calculate the percentage change compared to the previous quarter

Low_Percent_Change = 
IF(
    NOT(ISBLANK([LowCount_PrevQuarter])),
    DIVIDE([LowCount] - [LowCount_PrevQuarter], [LowCount_PrevQuarter], 0),
    BLANK()
)

Moderate_Percent_Change = 
IF(
    NOT(ISBLANK([ModerateCount_PrevQuarter])),
    DIVIDE([ModerateCount] - [ModerateCount_PrevQuarter], [ModerateCount_PrevQuarter], 0),
    BLANK()
)

5. now you can, create a Narrative Measure

Narrative = 
VAR CurrentPeriod = SELECTEDVALUE('YourTable'[Period_1])
VAR LowCurrent = [LowCount]
VAR LowPrevious = [LowCount_PrevQuarter]
VAR LowPercentChange = [Low_Percent_Change] * 100

VAR ModerateCurrent = [ModerateCount]
VAR ModeratePrevious = [ModerateCount_PrevQuarter]
VAR ModeratePercentChange = [Moderate_Percent_Change] * 100

RETURN 
SWITCH(
    TRUE(),
    CurrentPeriod = "Jun 23", "Insufficient data",
    CurrentPeriod = "All period" || CurrentPeriod = "Jun 24",
        "In Jun 24, there were " & LowCurrent & " low performers (" & 
        IF(ISBLANK(LowPercentChange), "no change", 
            FORMAT(LowPercentChange, "0") & "% " & 
            IF(LowPercentChange < 0, "reduction", "increase") & 
            " compared to Mar 24"
        ) & 
        "); " & ModerateCurrent & " moderate performers (" & 
        IF(ISBLANK(ModeratePercentChange), "no change", 
            FORMAT(ModeratePercentChange, "0") & "% " & 
            IF(ModeratePercentChange < 0, "reduction", "increase") & 
            " compared to Mar 24"
        ) & ").",
    "In " & CurrentPeriod & ", there were " & LowCurrent & " low performers (" & 
    IF(ISBLANK(LowPercentChange), "no change", 
        FORMAT(LowPercentChange, "0") & "% " & 
        IF(LowPercentChange < 0, "reduction", "increase") & 
        " compared to the previous quarter"
    ) & 
    "); " & ModerateCurrent & " moderate performers (" & 
    IF(ISBLANK(ModeratePercentChange), "no change", 
        FORMAT(ModeratePercentChange, "0") & "% " & 
        IF(ModeratePercentChange < 0, "reduction", "increase") & 
        " compared to the previous quarter"
    ) & ")."
)

Hope this helps.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)

➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now

LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

View solution in original post

DataNinja777 · ‎11-03-2024

Hi @Marshmallow ,

You can achieve your required output by writing a variance commentary dax measure like below:

Commentary = 
    VAR CurrentQuarter = SELECTEDVALUE('Calendar'[YYYY Quarter])
    VAR PreviousQuarterDate = CALCULATE(MAX('Calendar'[Date]), DATEADD('Calendar'[Date], -1, QUARTER))
    
    VAR CurrentLowPerformerCount = [Low performer count]
    VAR CurrentModeratePerformerCount = [Moderate performer count]
    
    VAR PreviousLowPerformerCount = 
        CALCULATE(
            [Low performer count],
            DATEADD('Calendar'[Date], -1, QUARTER)
        )
    
    VAR PreviousModeratePerformerCount = 
        CALCULATE(
            [Moderate performer count],
            DATEADD('Calendar'[Date], -1, QUARTER)
        )

    VAR LowPerformerChange = 
        IF(
            NOT ISBLANK(PreviousLowPerformerCount),
            FORMAT(ABS(DIVIDE(CurrentLowPerformerCount - PreviousLowPerformerCount, PreviousLowPerformerCount, 0)), "0%"),
            "insufficient data"
        )

    VAR ModeratePerformerChange = 
        IF(
            NOT ISBLANK(PreviousModeratePerformerCount),
            FORMAT(ABS(DIVIDE(CurrentModeratePerformerCount - PreviousModeratePerformerCount, PreviousModeratePerformerCount, 0)), "0%"),
            "insufficient data"
        )

    VAR LowPerformerTrend = 
        IF(
            NOT ISBLANK(PreviousLowPerformerCount) && CurrentLowPerformerCount < PreviousLowPerformerCount,
            "a reduction of ",
            IF(
                NOT ISBLANK(PreviousLowPerformerCount),
                "an increase of ",
                ""
            )
        )

    VAR ModeratePerformerTrend = 
        IF(
            NOT ISBLANK(PreviousModeratePerformerCount) && CurrentModeratePerformerCount < PreviousModeratePerformerCount,
            "a reduction of ",
            IF(
                NOT ISBLANK(PreviousModeratePerformerCount),
                "an increase of ",
                ""
            )
        )

    RETURN
        "In " & CurrentQuarter & ", there were " & 
        CurrentLowPerformerCount & " low performers (" & LowPerformerTrend & LowPerformerChange & 
        " compared to the previous quarter); " & 
        CurrentModeratePerformerCount & " moderate performers (" & ModeratePerformerTrend & ModeratePerformerChange & 
        " compared to the previous quarter)."

The output would look as follows:

I have attached an example pbix for your reference.

Best regards,

View solution in original post

DataNinja777 · ‎11-03-2024

Hi @Marshmallow ,

You can produce your desired output by tweaking the DAX formula as shown below:

Commentary = 
    VAR CurrentQuarter = SELECTEDVALUE('Calendar'[YYYY Quarter])
    
    VAR CurrentLowPerformerCount = [Low performer count]
    VAR CurrentModeratePerformerCount = [Moderate performer count]
    
    VAR PreviousLowPerformerCount = 
        CALCULATE(
            [Low performer count],
            DATEADD('Calendar'[Date], -1, QUARTER)
        )
    
    VAR PreviousModeratePerformerCount = 
        CALCULATE(
            [Moderate performer count],
            DATEADD('Calendar'[Date], -1, QUARTER)
        )

    VAR LowPerformerChange = 
        IF(
            NOT ISBLANK(PreviousLowPerformerCount),
            FORMAT(ABS(DIVIDE(CurrentLowPerformerCount - PreviousLowPerformerCount, PreviousLowPerformerCount, 0)), "0%"),
            "insufficient data"
        )

    VAR ModeratePerformerChange = 
        IF(
            NOT ISBLANK(PreviousModeratePerformerCount),
            FORMAT(ABS(DIVIDE(CurrentModeratePerformerCount - PreviousModeratePerformerCount, PreviousModeratePerformerCount, 0)), "0%"),
            "insufficient data"
        )

    VAR LowPerformerTrend = 
        IF(
            NOT ISBLANK(PreviousLowPerformerCount),
            IF(CurrentLowPerformerCount < PreviousLowPerformerCount, "a reduction of ", "an increase of "),
            ""
        )

    VAR ModeratePerformerTrend = 
        IF(
            NOT ISBLANK(PreviousModeratePerformerCount),
            IF(CurrentModeratePerformerCount < PreviousModeratePerformerCount, "a reduction of ", "an increase of "),
            ""
        )

    VAR MinQuarter = CALCULATE(MIN('Calendar'[YYYY Quarter]), ALL('Calendar'))
    VAR MaxQuarter = CALCULATE(MAX('Calendar'[YYYY Quarter]), ALL('Calendar'))
    
    VAR TotalLowPerformerCount = CALCULATE([Low performer count], ALL('Calendar'))
    VAR TotalModeratePerformerCount = CALCULATE([Moderate performer count], ALL('Calendar'))

    RETURN
        IF(
            ISBLANK(CurrentQuarter),
            "In " & MinQuarter & " to " & MaxQuarter & 
            ", there were " & TotalLowPerformerCount & " low performers and " & 
            TotalModeratePerformerCount & " moderate performers.",
            "In " & CurrentQuarter & ", there were " & 
            CurrentLowPerformerCount & " low performers (" & LowPerformerTrend & LowPerformerChange & 
            " compared to the previous quarter); and " & 
            CurrentModeratePerformerCount & " moderate performers (" & ModeratePerformerTrend & ModeratePerformerChange & 
            " compared to the previous quarter)."
        )

The resulting output when no filter is applied is shown below:

I have attached an example pbix file for your reference.

Best regards,

View solution in original post

DataNinja777 · ‎11-11-2024

Hi @Marshmallow ,

You can produce your required output by writing the dax formula like below.

Commentary (updated) = 
    VAR CurrentQuarter = SELECTEDVALUE('Calendar'[YYYY Quarter])
    
    VAR CurrentLowPerformerCount = [Low performer count]
    VAR CurrentModeratePerformerCount = [Moderate performer count]
    VAR CurrentHighPerformerCount = [High performer count]
    
    VAR PreviousLowPerformerCount = 
        CALCULATE(
            [Low performer count],
            DATEADD('Calendar'[Date], -1, QUARTER)
        )
    
    VAR PreviousModeratePerformerCount = 
        CALCULATE(
            [Moderate performer count],
            DATEADD('Calendar'[Date], -1, QUARTER)
        )
        
    VAR PreviousHighPerformerCount = 
        CALCULATE(
            [High performer count],
            DATEADD('Calendar'[Date], -1, QUARTER)
        )

    VAR LowPerformerChange = 
        IF(
            NOT ISBLANK(PreviousLowPerformerCount),
            FORMAT(ABS(DIVIDE(CurrentLowPerformerCount - PreviousLowPerformerCount, PreviousLowPerformerCount, 0)), "0%"),
            "insufficient data"
        )

    VAR ModeratePerformerChange = 
        IF(
            NOT ISBLANK(PreviousModeratePerformerCount),
            FORMAT(ABS(DIVIDE(CurrentModeratePerformerCount - PreviousModeratePerformerCount, PreviousModeratePerformerCount, 0)), "0%"),
            "insufficient data"
        )

    VAR HighPerformerChange = 
        IF(
            NOT ISBLANK(PreviousHighPerformerCount),
            FORMAT(ABS(DIVIDE(CurrentHighPerformerCount - PreviousHighPerformerCount, PreviousHighPerformerCount, 0)), "0%"),
            "insufficient data"
        )

    VAR LowPerformerTrend = 
        IF(
            NOT ISBLANK(PreviousLowPerformerCount),
            IF(CurrentLowPerformerCount < PreviousLowPerformerCount, "a reduction of ", "an increase of "),
            ""
        )

    VAR ModeratePerformerTrend = 
        IF(
            NOT ISBLANK(PreviousModeratePerformerCount),
            IF(CurrentModeratePerformerCount < PreviousModeratePerformerCount, "a reduction of ", "an increase of "),
            ""
        )

    VAR HighPerformerTrend = 
        IF(
            NOT ISBLANK(PreviousHighPerformerCount),
            IF(CurrentHighPerformerCount < PreviousHighPerformerCount, "a reduction of ", "an increase of "),
            ""
        )

    VAR MinQuarter = CALCULATE(MIN('Calendar'[YYYY Quarter]), ALL('Calendar'))
    VAR MaxQuarter = CALCULATE(MAX('Calendar'[YYYY Quarter]), ALL('Calendar'))
    
    VAR TotalLowPerformerCount = CALCULATE([Low performer count], ALL('Calendar'))
    VAR TotalModeratePerformerCount = CALCULATE([Moderate performer count], ALL('Calendar'))
    VAR TotalHighPerformerCount = CALCULATE([High performer count], ALL('Calendar'))

    RETURN
        IF(
            ISBLANK(CurrentQuarter),
            "In " & MinQuarter & " to " & MaxQuarter & 
            ", there were " & TotalLowPerformerCount & " low performers, " & 
            TotalModeratePerformerCount & " moderate performers, and " & 
            TotalHighPerformerCount & " high performers.",
            
            "In " & CurrentQuarter & ", there were " & 
            CurrentLowPerformerCount & " low performers (" & 
            IF(
                LowPerformerChange = "0%",
                "no change to previous quarter",
                LowPerformerTrend & LowPerformerChange & " compared to the previous quarter"
            ) & "); " &
            
            CurrentModeratePerformerCount & " moderate performers (" & 
            IF(
                ModeratePerformerChange = "0%",
                "no change to previous quarter",
                ModeratePerformerTrend & ModeratePerformerChange & " compared to the previous quarter"
            ) & "); and " &
            
            CurrentHighPerformerCount & " high performers (" & 
            IF(
                HighPerformerChange = "0%",
                "no change to previous quarter",
                HighPerformerTrend & HighPerformerChange & " compared to the previous quarter"
            ) & ")."
        )

The resulting output is as shown below:

I have attached an example pbix file for your reference.

Best regards,

View solution in original post

Marshmallow · ‎11-03-2024

Hi @DataNinja777 , i select the measurement to become the fx for the text. When I select Period 'All' in the filter, it shows as below.

Can we make it to show 'In Jun 23 to Jun 24, there were [sub total of the low count] low performers and [sub total of the moderate count] moderate performers'.

Once I select Jun24 - it displays the correct commentary (picture below)

appreciate your help

DataNinja777 · ‎11-03-2024

Hi @Marshmallow ,

You can produce your desired output by tweaking the DAX formula as shown below:

Commentary = 
    VAR CurrentQuarter = SELECTEDVALUE('Calendar'[YYYY Quarter])
    
    VAR CurrentLowPerformerCount = [Low performer count]
    VAR CurrentModeratePerformerCount = [Moderate performer count]
    
    VAR PreviousLowPerformerCount = 
        CALCULATE(
            [Low performer count],
            DATEADD('Calendar'[Date], -1, QUARTER)
        )
    
    VAR PreviousModeratePerformerCount = 
        CALCULATE(
            [Moderate performer count],
            DATEADD('Calendar'[Date], -1, QUARTER)
        )

    VAR LowPerformerChange = 
        IF(
            NOT ISBLANK(PreviousLowPerformerCount),
            FORMAT(ABS(DIVIDE(CurrentLowPerformerCount - PreviousLowPerformerCount, PreviousLowPerformerCount, 0)), "0%"),
            "insufficient data"
        )

    VAR ModeratePerformerChange = 
        IF(
            NOT ISBLANK(PreviousModeratePerformerCount),
            FORMAT(ABS(DIVIDE(CurrentModeratePerformerCount - PreviousModeratePerformerCount, PreviousModeratePerformerCount, 0)), "0%"),
            "insufficient data"
        )

    VAR LowPerformerTrend = 
        IF(
            NOT ISBLANK(PreviousLowPerformerCount),
            IF(CurrentLowPerformerCount < PreviousLowPerformerCount, "a reduction of ", "an increase of "),
            ""
        )

    VAR ModeratePerformerTrend = 
        IF(
            NOT ISBLANK(PreviousModeratePerformerCount),
            IF(CurrentModeratePerformerCount < PreviousModeratePerformerCount, "a reduction of ", "an increase of "),
            ""
        )

    VAR MinQuarter = CALCULATE(MIN('Calendar'[YYYY Quarter]), ALL('Calendar'))
    VAR MaxQuarter = CALCULATE(MAX('Calendar'[YYYY Quarter]), ALL('Calendar'))
    
    VAR TotalLowPerformerCount = CALCULATE([Low performer count], ALL('Calendar'))
    VAR TotalModeratePerformerCount = CALCULATE([Moderate performer count], ALL('Calendar'))

    RETURN
        IF(
            ISBLANK(CurrentQuarter),
            "In " & MinQuarter & " to " & MaxQuarter & 
            ", there were " & TotalLowPerformerCount & " low performers and " & 
            TotalModeratePerformerCount & " moderate performers.",
            "In " & CurrentQuarter & ", there were " & 
            CurrentLowPerformerCount & " low performers (" & LowPerformerTrend & LowPerformerChange & 
            " compared to the previous quarter); and " & 
            CurrentModeratePerformerCount & " moderate performers (" & ModeratePerformerTrend & ModeratePerformerChange & 
            " compared to the previous quarter)."
        )

The resulting output when no filter is applied is shown below:

I have attached an example pbix file for your reference.

Best regards,

Marshmallow · ‎11-10-2024

i am trying to make a small modification if Performance Change is 0% then in the bracket says (no change to previous quarter). Below is what i am trying to do but keep failing.

RETURN

IF(ISBLANK(CurrentQuarter),

"In " & MinQuarter & " to " & MaxQuarter &

", there were " & TotalLowPerformerCount & " low performers, "&

TotalModeratePerformerCount & " moderate performers, and "&

TotalHighPerformerCount & " high performers. ",

"In " & CurrentQuarter & ", there were " &

CurrentLowPerformerCount & " low performers (" & LowPerformerTrend & LowPerformerChange &

" compared to the previous quarter); "&

CurrentModeratePerformerCount & " moderate performers (" & ModeratePerformerTrend & ModeratePerformerChange &

" compared to the previous quarter); and "&

CurrentHighPerformerCount & " high performers ("& If(HighPerformerChange < 0, "same rate as previous quarter", HighPerformerTrend & HighPerformerChange &

" compared to the previous quarter)."

))

Can @DataNinja777 or someone help please?

DataNinja777 · ‎11-11-2024

Hi @Marshmallow ,

You can produce your required output by writing the dax formula like below.

Commentary (updated) = 
    VAR CurrentQuarter = SELECTEDVALUE('Calendar'[YYYY Quarter])
    
    VAR CurrentLowPerformerCount = [Low performer count]
    VAR CurrentModeratePerformerCount = [Moderate performer count]
    VAR CurrentHighPerformerCount = [High performer count]
    
    VAR PreviousLowPerformerCount = 
        CALCULATE(
            [Low performer count],
            DATEADD('Calendar'[Date], -1, QUARTER)
        )
    
    VAR PreviousModeratePerformerCount = 
        CALCULATE(
            [Moderate performer count],
            DATEADD('Calendar'[Date], -1, QUARTER)
        )
        
    VAR PreviousHighPerformerCount = 
        CALCULATE(
            [High performer count],
            DATEADD('Calendar'[Date], -1, QUARTER)
        )

    VAR LowPerformerChange = 
        IF(
            NOT ISBLANK(PreviousLowPerformerCount),
            FORMAT(ABS(DIVIDE(CurrentLowPerformerCount - PreviousLowPerformerCount, PreviousLowPerformerCount, 0)), "0%"),
            "insufficient data"
        )

    VAR ModeratePerformerChange = 
        IF(
            NOT ISBLANK(PreviousModeratePerformerCount),
            FORMAT(ABS(DIVIDE(CurrentModeratePerformerCount - PreviousModeratePerformerCount, PreviousModeratePerformerCount, 0)), "0%"),
            "insufficient data"
        )

    VAR HighPerformerChange = 
        IF(
            NOT ISBLANK(PreviousHighPerformerCount),
            FORMAT(ABS(DIVIDE(CurrentHighPerformerCount - PreviousHighPerformerCount, PreviousHighPerformerCount, 0)), "0%"),
            "insufficient data"
        )

    VAR LowPerformerTrend = 
        IF(
            NOT ISBLANK(PreviousLowPerformerCount),
            IF(CurrentLowPerformerCount < PreviousLowPerformerCount, "a reduction of ", "an increase of "),
            ""
        )

    VAR ModeratePerformerTrend = 
        IF(
            NOT ISBLANK(PreviousModeratePerformerCount),
            IF(CurrentModeratePerformerCount < PreviousModeratePerformerCount, "a reduction of ", "an increase of "),
            ""
        )

    VAR HighPerformerTrend = 
        IF(
            NOT ISBLANK(PreviousHighPerformerCount),
            IF(CurrentHighPerformerCount < PreviousHighPerformerCount, "a reduction of ", "an increase of "),
            ""
        )

    VAR MinQuarter = CALCULATE(MIN('Calendar'[YYYY Quarter]), ALL('Calendar'))
    VAR MaxQuarter = CALCULATE(MAX('Calendar'[YYYY Quarter]), ALL('Calendar'))
    
    VAR TotalLowPerformerCount = CALCULATE([Low performer count], ALL('Calendar'))
    VAR TotalModeratePerformerCount = CALCULATE([Moderate performer count], ALL('Calendar'))
    VAR TotalHighPerformerCount = CALCULATE([High performer count], ALL('Calendar'))

    RETURN
        IF(
            ISBLANK(CurrentQuarter),
            "In " & MinQuarter & " to " & MaxQuarter & 
            ", there were " & TotalLowPerformerCount & " low performers, " & 
            TotalModeratePerformerCount & " moderate performers, and " & 
            TotalHighPerformerCount & " high performers.",
            
            "In " & CurrentQuarter & ", there were " & 
            CurrentLowPerformerCount & " low performers (" & 
            IF(
                LowPerformerChange = "0%",
                "no change to previous quarter",
                LowPerformerTrend & LowPerformerChange & " compared to the previous quarter"
            ) & "); " &
            
            CurrentModeratePerformerCount & " moderate performers (" & 
            IF(
                ModeratePerformerChange = "0%",
                "no change to previous quarter",
                ModeratePerformerTrend & ModeratePerformerChange & " compared to the previous quarter"
            ) & "); and " &
            
            CurrentHighPerformerCount & " high performers (" & 
            IF(
                HighPerformerChange = "0%",
                "no change to previous quarter",
                HighPerformerTrend & HighPerformerChange & " compared to the previous quarter"
            ) & ")."
        )

The resulting output is as shown below:

I have attached an example pbix file for your reference.

Best regards,

Marshmallow · ‎11-11-2024

Indeed a dataninja - just like the nickname @DataNinja777 . Thank you so so so muchly - it is now perfecto!!! 🎊🍔

DataNinja777 · ‎11-03-2024

Hi @Marshmallow ,

You can achieve your required output by writing a variance commentary dax measure like below:

Commentary = 
    VAR CurrentQuarter = SELECTEDVALUE('Calendar'[YYYY Quarter])
    VAR PreviousQuarterDate = CALCULATE(MAX('Calendar'[Date]), DATEADD('Calendar'[Date], -1, QUARTER))
    
    VAR CurrentLowPerformerCount = [Low performer count]
    VAR CurrentModeratePerformerCount = [Moderate performer count]
    
    VAR PreviousLowPerformerCount = 
        CALCULATE(
            [Low performer count],
            DATEADD('Calendar'[Date], -1, QUARTER)
        )
    
    VAR PreviousModeratePerformerCount = 
        CALCULATE(
            [Moderate performer count],
            DATEADD('Calendar'[Date], -1, QUARTER)
        )

    VAR LowPerformerChange = 
        IF(
            NOT ISBLANK(PreviousLowPerformerCount),
            FORMAT(ABS(DIVIDE(CurrentLowPerformerCount - PreviousLowPerformerCount, PreviousLowPerformerCount, 0)), "0%"),
            "insufficient data"
        )

    VAR ModeratePerformerChange = 
        IF(
            NOT ISBLANK(PreviousModeratePerformerCount),
            FORMAT(ABS(DIVIDE(CurrentModeratePerformerCount - PreviousModeratePerformerCount, PreviousModeratePerformerCount, 0)), "0%"),
            "insufficient data"
        )

    VAR LowPerformerTrend = 
        IF(
            NOT ISBLANK(PreviousLowPerformerCount) && CurrentLowPerformerCount < PreviousLowPerformerCount,
            "a reduction of ",
            IF(
                NOT ISBLANK(PreviousLowPerformerCount),
                "an increase of ",
                ""
            )
        )

    VAR ModeratePerformerTrend = 
        IF(
            NOT ISBLANK(PreviousModeratePerformerCount) && CurrentModeratePerformerCount < PreviousModeratePerformerCount,
            "a reduction of ",
            IF(
                NOT ISBLANK(PreviousModeratePerformerCount),
                "an increase of ",
                ""
            )
        )

    RETURN
        "In " & CurrentQuarter & ", there were " & 
        CurrentLowPerformerCount & " low performers (" & LowPerformerTrend & LowPerformerChange & 
        " compared to the previous quarter); " & 
        CurrentModeratePerformerCount & " moderate performers (" & ModeratePerformerTrend & ModeratePerformerChange & 
        " compared to the previous quarter)."

The output would look as follows:

I have attached an example pbix for your reference.

Best regards,

Sahir_Maharaj · ‎11-03-2024

Hello @Marshmallow,

Can you please try this approach:

1. create a measure that counts the number of "LOW" performers

LowCount = 
CALCULATE(
    COUNTROWS('YourTable'),
    'YourTable'[Rating] = "LOW"
)

2. create a measure for counting "MOD" (Moderate) performers

ModerateCount = 
CALCULATE(
    COUNTROWS('YourTable'),
    'YourTable'[Rating] = "MOD"
)

3. for each rating, create a measure to get the count of the previous quarter

LowCount_PrevQuarter = 
CALCULATE(
    [LowCount],
    PREVIOUSQUARTER('YourTable'[Period_1])
)

ModerateCount_PrevQuarter = 
CALCULATE(
    [ModerateCount],
    PREVIOUSQUARTER('YourTable'[Period_1])
)

4. calculate the percentage change compared to the previous quarter

Low_Percent_Change = 
IF(
    NOT(ISBLANK([LowCount_PrevQuarter])),
    DIVIDE([LowCount] - [LowCount_PrevQuarter], [LowCount_PrevQuarter], 0),
    BLANK()
)

Moderate_Percent_Change = 
IF(
    NOT(ISBLANK([ModerateCount_PrevQuarter])),
    DIVIDE([ModerateCount] - [ModerateCount_PrevQuarter], [ModerateCount_PrevQuarter], 0),
    BLANK()
)

5. now you can, create a Narrative Measure

Narrative = 
VAR CurrentPeriod = SELECTEDVALUE('YourTable'[Period_1])
VAR LowCurrent = [LowCount]
VAR LowPrevious = [LowCount_PrevQuarter]
VAR LowPercentChange = [Low_Percent_Change] * 100

VAR ModerateCurrent = [ModerateCount]
VAR ModeratePrevious = [ModerateCount_PrevQuarter]
VAR ModeratePercentChange = [Moderate_Percent_Change] * 100

RETURN 
SWITCH(
    TRUE(),
    CurrentPeriod = "Jun 23", "Insufficient data",
    CurrentPeriod = "All period" || CurrentPeriod = "Jun 24",
        "In Jun 24, there were " & LowCurrent & " low performers (" & 
        IF(ISBLANK(LowPercentChange), "no change", 
            FORMAT(LowPercentChange, "0") & "% " & 
            IF(LowPercentChange < 0, "reduction", "increase") & 
            " compared to Mar 24"
        ) & 
        "); " & ModerateCurrent & " moderate performers (" & 
        IF(ISBLANK(ModeratePercentChange), "no change", 
            FORMAT(ModeratePercentChange, "0") & "% " & 
            IF(ModeratePercentChange < 0, "reduction", "increase") & 
            " compared to Mar 24"
        ) & ").",
    "In " & CurrentPeriod & ", there were " & LowCurrent & " low performers (" & 
    IF(ISBLANK(LowPercentChange), "no change", 
        FORMAT(LowPercentChange, "0") & "% " & 
        IF(LowPercentChange < 0, "reduction", "increase") & 
        " compared to the previous quarter"
    ) & 
    "); " & ModerateCurrent & " moderate performers (" & 
    IF(ISBLANK(ModeratePercentChange), "no change", 
        FORMAT(ModeratePercentChange, "0") & "% " & 
        IF(ModeratePercentChange < 0, "reduction", "increase") & 
        " compared to the previous quarter"
    ) & ")."
)

Hope this helps.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)

➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now

LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Marshmallow · ‎11-03-2024

Hi @Sahir_Maharaj

Using the script provided, when the filter for the period is selected to 'ALL', the narrative shows' In , there were [total low count] low performers (no change); [total mod count] moderate performers (no change)."

How do we change it so it shows this narrative: From Jun 23 to Jun 24, there were [total low count] low performers; [total moderate count] mod performers

calculate % difference from previous quarter

Helpful resources

Join us at the Microsoft Fabric Community Conference

We want your feedback!

Microsoft Fabric Community Conference 2025

A Year in Review - December 2024

How to Get Your Question Answered Quickly

Join us at the 2025 Microsoft Fabric Community Conference

calculate % difference from previous quarter

Helpful resources

Join us at the Microsoft Fabric Community Conference

We want your feedback!

Microsoft Fabric Community Conference 2025

A Year in Review - December 2024

How to Get Your Question Answered Quickly