Need help creating narrative sentences where the text changes when the data updates

[RichOB]

Hi, I saw a way ages ago for adding text to a report where the key data points change as the source data updates. I want to write the sentences below in my report using the tabled data underneath it. I need it to be more quarter specific than the information that the Smart Narrative.

- Total amount of Incidents grew in Q2 to Q3 by X%

- LOCATION had the highest amount of Incidents from Q2 to Q3 by X%

- INCIDENT TYPE increased from Q2 to Q3 by X% with LOCATION having the most at X%

- INCIDENT TYPE decreased from Q2 to Q3 in X by X%BY LOCATION having the biggest decrease by X%

Incident Type	Location	Date	Financial Quarter
Missing person	Glasgow	02/08/2024	Q2
Missing person	Glasgow	02/08/2024	Q2
Medical concern	Glasgow	01/11/2024	Q3
Missing person	Glasgow	01/11/2024	Q3
Missing person	Glasgow	01/11/2024	Q3
Medical concern	Edinburgh	02/08/2024	Q2
Medical concern	Edinburgh	02/08/2024	Q2
Missing Person	Edinburgh	02/08/2024	Q2
Medical concern	Edinburgh	01/11/2024	Q3
Medical concern	Edinburgh	01/11/2024	Q3
Aggression	Manchester	02/08/2024	Q2
Aggression	Manchester	02/08/2024	Q2
Aggression	Manchester	01/11/2024	Q3
Aggression	Manchester	01/11/2024	Q3
Medical concern	Manchester	01/11/2024	Q3

Thanks for your help!

[RichOB]

Hi @FarhanJeelani  just checking in with this, the first step above you mention is making a table but I'm having trouble with the column count when you said:

• Count the number of incidents for each combination.
  
  

What measure did you add to the new table to get that column count please?

[FarhanJeelani]

Hi @RichOB 

To generate the sentences dynamically based on the data provided, we can break this down into steps using DAX . Here's how to create those insights accurately:

Steps to Implement:

1. Prepare a Pivot Table or Summarized Data Table

   • Group the data by Incident Type, Location, and Financial Quarter.
   • Count the number of incidents for each combination.

   Example:

   Incident Type Location Financial Quarter Count

   Missing Person	Glasgow	Q2	2
   Missing Person	Glasgow	Q3	3
   Medical Concern	Glasgow	Q3	1
   Medical Concern	Edinburgh	Q2	2
   Medical Concern	Edinburgh	Q3	2
   Aggression	Manchester	Q2	2
   Aggression	Manchester	Q3	2
   Medical Concern	Manchester	Q3	1

Key Measures:

1. Total Growth in Incidents (Q2 to Q3):

   TotalGrowthIncidents = 
   VAR Q2Incidents = CALCULATE(SUM('Table'[Count]), 'Table'[Financial Quarter] = "Q2")
   VAR Q3Incidents = CALCULATE(SUM('Table'[Count]), 'Table'[Financial Quarter] = "Q3")
   RETURN
   DIVIDE(Q3Incidents - Q2Incidents, Q2Incidents, 0)

   Output: Calculate the % change from Q2 to Q3.

1. Location with the Highest Growth in Incidents:

   LocationGrowth = 
   ADDCOLUMNS(
       SUMMARIZE('Table', 'Table'[Location]),
       "Growth", DIVIDE(
           CALCULATE(SUM('Table'[Count]), 'Table'[Financial Quarter] = "Q3") -
           CALCULATE(SUM('Table'[Count]), 'Table'[Financial Quarter] = "Q2"),
           CALCULATE(SUM('Table'[Count]), 'Table'[Financial Quarter] = "Q2"), 0
       )
   )
   RETURN TOPN(1, LocationGrowth, [Growth], DESC)

   Output: Location with the highest percentage growth.

1. Incident Type with the Highest Increase:

   IncidentTypeGrowth = 
   ADDCOLUMNS(
       SUMMARIZE('Table', 'Table'[Incident Type]),
       "Growth", DIVIDE(
           CALCULATE(SUM('Table'[Count]), 'Table'[Financial Quarter] = "Q3") -
           CALCULATE(SUM('Table'[Count]), 'Table'[Financial Quarter] = "Q2"),
           CALCULATE(SUM('Table'[Count]), 'Table'[Financial Quarter] = "Q2"), 0
       )
   )
   RETURN TOPN(1, IncidentTypeGrowth, [Growth], DESC)

   Output: Incident type with the highest percentage growth and associated location.

1. Incident Type with the Largest Decrease:

   IncidentTypeDecrease = 
   ADDCOLUMNS(
       SUMMARIZE('Table', 'Table'[Incident Type]),
       "Decrease", DIVIDE(
           CALCULATE(SUM('Table'[Count]), 'Table'[Financial Quarter] = "Q3") -
           CALCULATE(SUM('Table'[Count]), 'Table'[Financial Quarter] = "Q2"),
           CALCULATE(SUM('Table'[Count]), 'Table'[Financial Quarter] = "Q2"), 0
       )
   )
   RETURN TOPN(1, IncidentTypeDecrease, [Decrease], ASC)

   Output: Incident type with the largest percentage decrease and associated location.

Narrative Example Output:

Once you've calculated the measures, use Smart Narrative or custom text boxes in Power BI to display the dynamic sentences. Example:

• "The total amount of incidents grew in Q2 to Q3 by 20%."
• "Glasgow had the highest amount of incidents from Q2 to Q3, growing by 25%."
• "Medical concern incidents increased from Q2 to Q3 by 30%, with Glasgow having the most at 40%."
• "Aggression incidents decreased from Q2 to Q3 in Manchester by 10%, with Manchester having the largest decrease of 15%."

This approach ensures your report updates dynamically as new data is ingested, and narratives remain accurate and quarter-specific.

Please mark this as solution if it helps you. Appreciate Kudos.

[RichOB]

I've made the summarized data table but I'm having trouble getting the Count column.:

Column_count = COUNT('Incidents'[Type of CCIA])

This is giving me the total of 15 in each cell instead of a 1 count. How can I correct that please?