Looking for help creating a summary table

[MattL86]

Hi all first post!

I am looking for some help, I am creating a tracking sheet to keep track of student induction materials (it pulls data direcrtly from our other systems). The base data is all well and good but I now need to create a short summary table with each of the pieces of mandatory training. I created a custom summary table which pulled together all of the summary measures I have made, and whilst this works fine, it is unaffected by the slicers I have. However when I display the measures in other visuals individually they are affected. So I guess I am wondering is there either a way to make the custom table affected by the slicers or is there a visual into which I can input my various measures that would display them as needed?

This is the table as we want it

Module	Complete	Incomplete
Essential Content - Health and Wellbeing	5701	201
Essential Content – Alcohol Safety	4859	1043
Essential Content - Consent	4923	979
Essential Content – Drug Safety	5203	699
Essential Content – Suicide Awareness	5109	793
Essential Content – Active Bystander	5125	777

Where each of the numers is calculated from a single table holding the data along with the qualities we want to be able to filter by.

The measure are calculated using the following formula

CALCULATE(COUNT('Induction Tracking'[Essential Content – Suicide Awareness]),'Induction Tracking'[Essential Content – Suicide Awareness]="Complete")

Any help would be greatly appreciated

[grazitti_sapna · Accepted solution]

Hi @MattL86 ,

To make your custom summary table responsive to slicers in Power BI, there are a few steps you can follow:

• Ensure Relationships are Set Up Correctly: Make sure your base table with the mandatory training information is properly linked to any slicer tables (e.g., date or department slicers). The relationships should be correctly set to either one-to-many or many-to-one, depending on your data model.
• Use a Matrix Visual Instead: Instead of a custom summary table, you can use a Matrix visual, which allows you to display your measures in rows and columns, and it responds well to slicers. Here's how to set it up:
  • Place the Module (e.g., “Essential Content - Health and Wellbeing”) in the Rows.
  • Add your measures (e.g., “Complete” and “Incomplete”) in the Values section.
• Dynamic Measures: If you want the matrix to react dynamically to slicers, ensure that your measures are written using DAX in a way that they respect the slicers. Your existing measure seems fine, but ensure it's set up properly with context:  Complete_Suicide_Awareness =
  CALCULATE(
  COUNTROWS('Induction Tracking'),
  'Induction Tracking'[Essential Content – Suicide Awareness] = "Complete")                                                                                Repeat a similar formula for the “Incomplete” status.
• Create a Custom Table Using DAX: If you still want to use a custom table (not the Matrix visual), you can create a Calculated Table using DAX that pulls data dynamically:                                                                                                                      SummaryTable =
  SUMMARIZE(
  'Induction Tracking',
  'Induction Tracking'[Module],
  "Complete", [Complete Measure],
  "Incomplete", [Incomplete Measure])                                                                                                                                      Ensure that the measures [Complete Measure] and [Incomplete Measure] reference the base table directly and are responsive to slicers.  
• Use a Card Visual for Individual Measures: If you prefer to display measures individually in a table format, you can set up Card visuals for each measure and arrange them side by side in a way that resembles a table. Cards respond to slicers by default.

This approach should allow you to achieve the summary table you want, with slicers affecting the displayed values dynamically. Let me know if you need further details on any step!

If I have resolved your question, please consider marking my post as a solution🎉. Thank you!

[v-yajiewan-msft · Accepted solution]

Hi @MattL86 , hello grazitti_sapna, thank you for your prompt reply!

Because the summary table is implemented through a calculated table, it is determined at the time of model creation and therefore will not respond to the slicer in the report.

As a workaround, we suggest using the table or matrix visual to display the measures you have created.

This will ensure that the data updates dynamically based on the slicer selections.

Additionally, please check that the fields used by your slicer are associated with the fields involved in the visual.

If the slicer's fields are not directly associated with the visual, the slicer will not be able to filter the relevant data.

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[grazitti_sapna · Accepted solution]

Hi @MattL86 ,

To make your custom summary table responsive to slicers in Power BI, there are a few steps you can follow:

• Ensure Relationships are Set Up Correctly: Make sure your base table with the mandatory training information is properly linked to any slicer tables (e.g., date or department slicers). The relationships should be correctly set to either one-to-many or many-to-one, depending on your data model.
• Use a Matrix Visual Instead: Instead of a custom summary table, you can use a Matrix visual, which allows you to display your measures in rows and columns, and it responds well to slicers. Here's how to set it up:
  • Place the Module (e.g., “Essential Content - Health and Wellbeing”) in the Rows.
  • Add your measures (e.g., “Complete” and “Incomplete”) in the Values section.
• Dynamic Measures: If you want the matrix to react dynamically to slicers, ensure that your measures are written using DAX in a way that they respect the slicers. Your existing measure seems fine, but ensure it's set up properly with context:  Complete_Suicide_Awareness =
  CALCULATE(
  COUNTROWS('Induction Tracking'),
  'Induction Tracking'[Essential Content – Suicide Awareness] = "Complete")                                                                                Repeat a similar formula for the “Incomplete” status.
• Create a Custom Table Using DAX: If you still want to use a custom table (not the Matrix visual), you can create a Calculated Table using DAX that pulls data dynamically:                                                                                                                      SummaryTable =
  SUMMARIZE(
  'Induction Tracking',
  'Induction Tracking'[Module],
  "Complete", [Complete Measure],
  "Incomplete", [Incomplete Measure])                                                                                                                                      Ensure that the measures [Complete Measure] and [Incomplete Measure] reference the base table directly and are responsive to slicers.  
• Use a Card Visual for Individual Measures: If you prefer to display measures individually in a table format, you can set up Card visuals for each measure and arrange them side by side in a way that resembles a table. Cards respond to slicers by default.

This approach should allow you to achieve the summary table you want, with slicers affecting the displayed values dynamically. Let me know if you need further details on any step!

If I have resolved your question, please consider marking my post as a solution🎉. Thank you!