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

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.

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?