In Search of an Efficient Approach for Grouping and Analyzing Measures

[Marc76]

Hey everyone!

I will now present the scenario I have, and I need your help to see how you would approach it. I have “solved” it in an incorrect way because my solution consumes too many resources and is inefficient

I have 5 measures, measure1, measure2 … measure5.

These measures visually need to be grouped (as a visual separation without totaling) into two groups:

Group 1: measure1 …3

Group 2: measure4, measure5

For each measure, I have:

Budget, Real, Budget LY, Real LY, Budget YTD, Real YTD, Budget LYTD, Real LYTD.

I have 2 companies:

Company 1, Company 2.

The measures were developed before the existence of calculation groups (but I don’t know if the solution involves calculation groups).

Now I will show you the grid structure I am looking to visualize.

I need your help on how you would approach the problem.

Thanks in advance.

[Poojara_D12 · Accepted solution]

Hi @Marc76 

Thank you for sharing the structure! Based on your requirements, the ideal approach to improve efficiency and avoid redundancy is to leverage Calculation Groups in Power BI. Calculation Groups allow you to create a set of dynamic measures (like Budget, Real, LY, YTD, etc.) that can be applied across different base measures (Measure1, Measure2, etc.), making the model more efficient.

Here's a suggested approach:

1. Create Calculation Groups for the Metrics

In Power BI, you can create Calculation Groups using Tabular Editor (either the free external Tabular Editor or the built-in Tabular Editor in Power BI Premium).

1. Open Tabular Editor:

   • In Power BI, go to External Tools > Tabular Editor.

2. Create a Calculation Group for metrics like Budget, Real, LY, and YTD:

   • In Tabular Editor, right-click on Tables and select Create New > Calculation Group. Name it Metrics.
   • Under this Calculation Group, create Calculation Items for each metric (e.g., Budget, Real, Budget LY, Real LY, Budget YTD, Real YTD, Budget LYTD, Real LYTD).

   For example:

   • Calculation Item: Budget
     SELECTEDMEASURE()
   • Calculation Item: Real
     SELECTEDMEASURE()
   • Calculation Item: Budget LY
     (Add the DAX logic to calculate last year's budget for each measure.)
   • Calculation Item: Real YTD
     (Add the DAX logic to calculate the year-to-date Real value for each measure.)

   Repeat for all required metrics.

2. Apply Calculation Group to Measures

• Since Calculation Groups work with any measure, you only need to create your five base measures (Measure1, Measure2, Measure3, etc.) once, without duplicating them for each metric.
• You can add these base measures in your table visual, and use the Calculation Group to dynamically switch between the metrics.

3. Set Up the Table Visual in Power BI

• In Power BI, use a Matrix visual to display the data.
• Put Company in the Columns field.
• Put Month-Year (M-Y) in the Rows field to handle the date filtering.
• Put Calculation Items (from the Metrics Calculation Group) in the Columns to dynamically display Budget, Real, LY, YTD, etc.

4. Grouping the Measures (Visual Grouping)

To achieve visual grouping (without totaling), you can add a Dummy Group column to your measures in the model and use it to create groups like Group 1 and Group 2. This can be done by creating a new column in your measure table.

For example:

Measure Group = 
SWITCH(
    TRUE(),
    [Measure] IN {"Measure1", "Measure2", "Measure3"}, "Group 1",
    [Measure] IN {"Measure4", "Measure5"}, "Group 2",
    BLANK()
)

Then, add this Measure Group to your Rows in the Matrix visual for grouping.

Final Result

This approach will:

• Enable you to dynamically switch between Budget, Real, LY, YTD, etc., using the Calculation Group without duplicating each measure.
• Reduce resource consumption by only calculating the necessary values on the fly.
• Allow visual grouping of measures into "Group 1" and "Group 2" without needing to sum the groups.

This method should be significantly more efficient than creating individual measures for each metric and year.

Let me know if you need further clarification!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant

[Fowmy]

@Marc76 

This could be done either using Calculation Group + Disconnected table or Disconnected table only.
For later, first, you need to define the levels GROUP, MEASURE, and BREAKUP(Budget, Real...) then build your measures based on the filter context of these fileds in the Disconnected table.