March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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.
Solved! Go to Solution.
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.
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,
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,
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,
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
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,
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.
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,
Indeed a dataninja - just like the nickname @DataNinja777 . Thank you so so so muchly - it is now perfecto!!! 🎊🍔
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,
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.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
91 | |
90 | |
83 | |
73 | |
49 |
User | Count |
---|---|
167 | |
149 | |
98 | |
73 | |
57 |