Forum Discussion
Kudo CommanderMatrix column order with Calculation Groups (Measures grouped under Calculation Items instead of the
Hi everyone,
I’m working with a Calculation Group for Time Intelligence in Power BI. My goal is to avoid creating separate measures like LY, YTD, MTD, QTD, LYTD, etc. for every base measure (we have around 50 key metrics). Instead, I created a Calculation Group and apply the logic dynamically.
The issue I’m facing is related to how the Matrix visual handles the columns.
What I want is something like this structure:
Traffic
– Date
– Vs LY%
Net Sales
– Date
– Vs LY%
Orders
– Date
– Vs LY%
But because the Calculation Group sits in the Columns bucket, Power BI does the opposite:
Date
– Traffic
– Net Sales
– Orders
Vs LY%
– Traffic
– Net Sales
– Orders
Basically, the Calculation Group becomes the outer layer and the Measures become the inner layer.
What I need is the Excel-style flexibility to “swap” who groups who (Measures grouping Calculation Items, instead of Calculation Items grouping Measures).
Is there any supported way to:
• Reverse this grouping hierarchy?
• Force Measures → Calculation Items order?
• Or any workaround to get the visual to display in the desired layout?
Thanks in advance, and yes, I know the Calculation Group is doing exactly what it’s designed to do… but I’m hoping there’s a trick I’m missing 😅
10 Replies
- danextian
Super User
Hi rampie
Pivot tables are probably the closest Excel equivalent to Power BI matrices, but one feature they have that matrices lack is the ability to move measures to a different level in the hierarchy. A workaround is to create a disconnected table and reference it in a measure. Then, use the column from that table along with the measure and the calculation group in a matrix.
Please see the attached pbix.
- danextian
Always validate an AI-generated response. There's already too much AI slops in the community. This is inaccurate. Power BI does not allow placing a value at the outer level of a column or row header.
- lbendlin
Consider using Visual Calculations instead, (or DAX User Defined Functions, or column header reference tables or Field Parameters...)
rampieHello AshokKunwar
This is not showing "In the Rows section, you will now see a new pill named "Values"."
- danextian
As mentioned in my initial reply to this user, this isn't possible. Very likely an unvalidated AI-generated response. You can report that post so we see less AI slops .
v-sgandrathiCommunity Support
- rampie
Hi v-sgandrathi
the contribution is valid and works as described. However, I don’t see it as the most viable approach when dealing with multiple metrics or when trying to replicate the same layout across different metric groups.Maintaining a manual table introduces additional overhead and reduces the dynamic nature of the model, especially at scale.
As of today, there is still no native way in Power BI to achieve this behavior in the Matrix visual, similar to how Excel allows swapping column hierarchies.
I’ve just published an idea requesting this feature, any support by voting would be greatly appreciated.
- v-sgandrathi
Hi rampie,
You’re right, the workaround does work, but Power BI Matrix visuals don’t currently allow you to swap the hierarchy between Measures and Calculation Group items like you can in Excel pivot tables. Using manual or disconnected tables can add extra maintenance and may not be ideal for larger or more dynamic models.
Thank you for submitting this as a feature request in the Ideas forum. Your feedback is helpful for the product team to understand real-world modeling needs.
Fabric Ideas - Microsoft Fabric Community
If you have any further questions or need assistance, please feel free to let us know.Thank you.
- v-sgandrathi
Hi rampie,
Following up to check whether you got a chance to review the suggestions given. If the issue still persists please let us know. Glad to help.
Thank you.
- HarishKM
rampie Hey,
Short answer: Not supported directly.
But you can use In a Matrix, anything in Rows/Columns forms the outer hierarchy; “Values” (measures) are always the innermost. A Calculation Group item placed on Columns will always sit outside the measures. You can’t flip that natively.
Workarounds
1) Best bet : Measure Field Parameter (puts measures on Columns, then nest Calc Group under them)- Modeling > New parameter > Fields > Measures. Add your base measures (Traffic, Net Sales, Orders…).
- In the Matrix:
- Columns: [Measure Parameter] first, then your Calc Group (e.g., Time Intelligence[Name]) second.
- Values: the auto-generated parameter measure (e.g., [Measure Parameter Value]).- Result: Columns = Measure → Calculation Item (your desired order).
- Tips: Use the Calc Group Ordinal to control Date, Vs LY%, etc. Reorder the parameter entries to control measure order.
2) If rows are acceptable: Show measures first on Rows
- Put the Calculation Group on Rows.
- Turn on Matrix > Values > “Show values on rows”.
- Result: Rows = Measure → Calculation Item (same logic, but on rows).
3) If Field Parameters aren’t available:
- Either duplicate measures per calc item (e.g., CALCULATE([Net Sales], 'Time Calc'[Name]="YTD")), or
- Use multiple matrices side by side (one per measure), aligning headers. Not elegant, but works.
Bottom line: You can’t invert the hierarchy with a Calculation Group alone. Use a Measure Field Parameter to make measures a true column level, then place the Calculation Group beneath it.
Thanks
Haish K
If I resolve your issue. Kindly give kudos to this post and accept it as a solution so other can refer this.
