Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Marshmallow
Helper I
Helper I

calculate % difference from previous quarter

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.

 

RatingPeriod_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.

4 ACCEPTED SOLUTIONS
Sahir_Maharaj
Super User
Super User

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
Super User
Super User

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:

DataNinja777_0-1730650401096.png

I have attached an example pbix for your reference.

 

Best regards,

 

View solution in original post

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:

DataNinja777_0-1730694499276.png

   

I have attached an example pbix file for your reference.

 

Best regards,

View solution in original post

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:

DataNinja777_0-1731314988615.png

I have attached an example pbix file for your reference.

 

Best regards,

 

 

 

View solution in original post

8 REPLIES 8
Marshmallow
Helper I
Helper I

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. 

Marshmallow_0-1730687214532.png

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)

Marshmallow_1-1730687354699.png

 

 

appreciate your help

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:

DataNinja777_0-1730694499276.png

   

I have attached an example pbix file for your reference.

 

Best regards,

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?

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:

DataNinja777_0-1731314988615.png

I have attached an example pbix file for your reference.

 

Best regards,

 

 

 

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

DataNinja777
Super User
Super User

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:

DataNinja777_0-1730650401096.png

I have attached an example pbix for your reference.

 

Best regards,

 

Sahir_Maharaj
Super User
Super User

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

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.