Pivot tables like excel

[AndyDD_UK]

I've been experimenting including with calculation groups to create excel type pivot tables but can't crack it, can you ?

Want 2 options, both based on selected metrics (from slicer) and selected month(s) and years (from slicer):

Option 1:

Columns show eg Jan 2025 sales, Jan 2025 profit, Feb 2025 sales, Feb 2025 profit etc...

Or

Option 2 g Jan 2025 sales, Feb 2025 sales, Jan 2025 profit, , Feb 2025 profit etc...

[v-ssriganesh]

Hello @AndyDD_UK,

Hope everything’s going great on your end! Just checking in has the issue been resolved, or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[v-ssriganesh]

Hello @AndyDD_UK,
We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.
Thank you.

[danextian · Accepted solution]

Hi @AndyDD_UK 

You'll need to create a disconnected table with one column listing all possible combinations of Month, Year, and the measures, and another column specifying the sort order for those values. Instead of concatenating Month and Year with the measure names, I would use another disconnected table to materialize the measure results in a column. This approach allows for greater flexibility in adjusting their hierarchy—especially since the matrix visual currently doesn't support placing measures above column headers in the hierarchy unlike in Excel.

Please see the attached sample pbix.

[v-ssriganesh]

Hello @AndyDD_UK,
Thank you for reaching out to the Microsoft Fabric Forum Community.

I have reproduced your scenario in Power BI using dynamic DAX and matrix visuals and I was able to achieve both Option 1 and Option 2 layouts just as you described.

Please note: While Power BI can mimic Excel-style pivot tables to a large extent, Excel and Power BI behave differently especially in how they handle dynamic column ordering, measure layout, and calculation context. So, a one-to-one Excel pivot structure is not always directly replicable, but the logic can still be implemented using DAX and modeling tricks.

For your reference, I’m attaching the working .pbix file so you can explore, reuse or modify the solution as needed.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-ssriganesh]

Hello @AndyDD_UK,
I hope the information provided has been useful. Please let me know if you need further clarification or would like to continue the discussion.
If your question has been answered, please “Accept as Solution” and Give “Kudos” so others with similar issues can easily find the resolution.
Thank you.

[lbendlin]

Power BI is not Excel.  If you need Excel specific functionality then you should consider staying with Excel.

"Fighting the API"  by trying to make one app behave like a different app is ultimately futile.

[burakkaragoz]

Hi @AndyDD_UK ,

Yeah, this is a pretty common challenge when trying to replicate Excel-style pivot tables in Power BI. What you're aiming for is a dynamic column layout that changes based on slicer selections — and that’s where things get a bit tricky.

To get either of your options working, you’ll likely need to use calculation groups in combination with a disconnected date table and some dynamic column headers via DAX. Here's a rough outline of how you can approach it:

1. Create a disconnected date table that includes Month-Year combinations (e.g. "Jan 2025").
2. Use a calculation group to define your metrics (Sales, Profit, etc.).
3. Then, create a matrix visual where the columns come from the disconnected date table and the values come from the calculation group.
4. Use DAX to filter the base measures based on the selected month/year from the slicer — this part is key to keeping the matrix dynamic.

For Option 1 (Month-Metric order), you can concatenate the month and metric in the column headers like "Jan 2025 - Sales", "Jan 2025 - Profit", etc.

For Option 2 (Metric-Month order), you’d flip the logic and build headers like "Sales - Jan 2025", "Sales - Feb 2025", etc.

It’s not super straightforward, but totally doable with some creative modeling. If you want, I can mock up a sample PBIX structure to show how this works.

Let me know which option you’re leaning toward and I’ll try to tailor the approach a bit more.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[danextian]

For Option 1 (Month-Metric order), you can concatenate the month and metric in the column headers like "Jan 2025 - Sales", "Jan 2025 - Profit", etc.

This doesnt make sense. How is OP supposed to concantenate the headers?  Year and Calc Group Items will be be in seperate hierarchies and they are from different tables as well.

[burakkaragoz]

Thanks for the feedback. What you say about the hierarchies being from different tables makes a lot of sense.

I was trying to simulate something like “January 2025 - Sales” as column header using a disconnected date table + measure group, but yes, I hit the same wall with the matrix visuals not allowing you to join those fields directly.

Just curious, are there any tricks you've seen people work around this? For example, it could be like this:

Creating a bridge table with pre-merged values (Lunar-Metric) and using that in the matrix?
Or somehow using field parameters to simulate this layout?
Or maybe using a table image instead of a matrix?
I know this is not a native behavior, but I'm wondering if there is a way to approach Excel-style layout.

I would appreciate your thoughts.