Forum Discussion
Impactful IndividualGrouping Measures into categories
I thought this would be easy, but cant work it out.
I'm trying to replicate this excel report
each line is an unrelated measure, but they related to different parts of the business hence the Objective grouping column
I tried using field parameters which works if only 1 group
Scorecard Measures = {
("Enquiry Count", NAMEOF('Metrics CRM'[Enquiry Count]), 0,"Accelerate growth via acquisition"),
("Qualified Enquiry Count", NAMEOF('Metrics CRM'[Qualified Enquiry Count]), 1,"Accelerate growth via acquisition"),
("SQL Count", NAMEOF('Metrics CRM'[SQL Count]), 2,"Accelerate growth via acquisition"),
("Assessments Taking Place", NAMEOF('Metrics CRM'[Assessments Taking Place]), 3,"Accelerate growth via acquisition")
}but cant add a second grouping as that 4th column has to have same name.
Is there an easy way to just add a simple grouping hierachy to this list of 30ish measures? Guessing there is some disconencted table and switch function that might work?
Appreicate any advice as cant seem to find an apporpriate solution in previous posts.
For this scenario, Field Parameters alone won't give you a true hierarchy/grouping of measures, because the parameter table is designed to select fields/measures dynamically, not to create multi-level group structures.
A better approach is to use a disconnected table + SWITCH measure pattern (or a calculation group if you have Tabular Editor available).
Option 1: Disconnected table + SWITCH (common approach)
Create a table like:
Objective Measure Name Sort
Accelerate growth via acquisition Enquiry Count 1 Accelerate growth via acquisition Qualified Enquiry Count 2 Accelerate growth via acquisition SQL Count 3 Improve retention Customer Count 4 Improve retention Churn Rate 5 Then create a slicer using Objective and a matrix using Measure Name.
Create a dynamic measure:
Selected Metric Value = SWITCH( SELECTEDVALUE('Measure Selector'[Measure Name]), "Enquiry Count", [Enquiry Count], "Qualified Enquiry Count", [Qualified Enquiry Count], "SQL Count", [SQL Count], "Customer Count", [Customer Count], "Churn Rate", [Churn Rate] )Now your matrix can display:
Objective └── Measure Name Value Enquiry Count 120 Qualified Enquiry 80 SQL Count 45 Objective └── Measure Name Value Customer Count 500 Churn Rate 5%Option 2: Calculation Groups (recommended for enterprise models)
If you use Tabular Editor, calculation groups provide a cleaner solution because you can manage many measures without creating a very large SWITCH statement.
You can create:
Calculation items for your metrics.
Metadata columns for grouping/objectives.
Better governance and easier maintenance.
Option 3: Keep Field Parameters + add a supporting dimension
You can also keep your existing Field Parameter and create a separate mapping table:
Field Parameter Item | | Measure Metadata Table | | Objective GroupThen use the metadata table for grouping/filtering.
For around 30 measures, I would recommend the disconnected table + SWITCH approach because it is simple, transparent, and easy to maintain. If this grows into hundreds of KPIs across multiple reports, then calculation groups become the better long-term design.
For more information:
Field parameters in Power BI: https://learn.microsoft.com/power-bi/create-reports/power-bi-field-parameters
Calculation groups: https://learn.microsoft.com/analysis-services/tabular-models/calculation-groups
💡 Helpful? Give a Kudos 👍 — keep the community growing.
✅ Solved your issue? Mark this as the Accepted Solution ✔️
Best regards, Prince Singh | Data Science & Microsoft Fabric Enthusiast
5 Replies
- Prince0011
Solution Sage
For this scenario, Field Parameters alone won't give you a true hierarchy/grouping of measures, because the parameter table is designed to select fields/measures dynamically, not to create multi-level group structures.
A better approach is to use a disconnected table + SWITCH measure pattern (or a calculation group if you have Tabular Editor available).
Option 1: Disconnected table + SWITCH (common approach)
Create a table like:
Objective Measure Name Sort
Accelerate growth via acquisition Enquiry Count 1 Accelerate growth via acquisition Qualified Enquiry Count 2 Accelerate growth via acquisition SQL Count 3 Improve retention Customer Count 4 Improve retention Churn Rate 5 Then create a slicer using Objective and a matrix using Measure Name.
Create a dynamic measure:
Selected Metric Value = SWITCH( SELECTEDVALUE('Measure Selector'[Measure Name]), "Enquiry Count", [Enquiry Count], "Qualified Enquiry Count", [Qualified Enquiry Count], "SQL Count", [SQL Count], "Customer Count", [Customer Count], "Churn Rate", [Churn Rate] )Now your matrix can display:
Objective └── Measure Name Value Enquiry Count 120 Qualified Enquiry 80 SQL Count 45 Objective └── Measure Name Value Customer Count 500 Churn Rate 5%Option 2: Calculation Groups (recommended for enterprise models)
If you use Tabular Editor, calculation groups provide a cleaner solution because you can manage many measures without creating a very large SWITCH statement.
You can create:
Calculation items for your metrics.
Metadata columns for grouping/objectives.
Better governance and easier maintenance.
Option 3: Keep Field Parameters + add a supporting dimension
You can also keep your existing Field Parameter and create a separate mapping table:
Field Parameter Item | | Measure Metadata Table | | Objective GroupThen use the metadata table for grouping/filtering.
For around 30 measures, I would recommend the disconnected table + SWITCH approach because it is simple, transparent, and easy to maintain. If this grows into hundreds of KPIs across multiple reports, then calculation groups become the better long-term design.
For more information:
Field parameters in Power BI: https://learn.microsoft.com/power-bi/create-reports/power-bi-field-parameters
Calculation groups: https://learn.microsoft.com/analysis-services/tabular-models/calculation-groups
💡 Helpful? Give a Kudos 👍 — keep the community growing.
✅ Solved your issue? Mark this as the Accepted Solution ✔️
Best regards, Prince Singh | Data Science & Microsoft Fabric Enthusiast
masplinI tried the switch version and got one issue. Al the formatting has benelost so every result is a 2 digit decimal. In the underlying model they are formatted whole numbers or %. Is there a way to keep the source formatting or you have to add the format to the switch for each measure?
- Selva-Salimi
Hi masplin ,
you can add a column in your disconnected table which include 1 and 2, 1 for numbers and 2 for percent. then in your measure add
IF ( table[flag] = 1 , selected metric value, FORMAT (selected metric value , "0.0%"))
If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution ✔️ to help the other members find it more quickly.
- danextian
Super User
Hi masplin
Adding a category column to a field parameter table can help group fields in a slicer, but it won't create a true hierarchy. Field parameters are essentially metadata instructions that tell Power BI which field or measure to substitute into a visual; they don't establish a parent-child relationship between the category and the parameter items.
If you need to display the fields as a hierarchy, you'll need to use a separate display or disconnected table and map the selected item back to the appropriate field or measure.
Please see the attached pbix.
- masplin
that was fast!!!
For the calculation group version presumably create a new calculation group add each of the measures as a calculation item. I dont understand the "Metadata columns for grouping/objectives." bit?
