Need Help Replicating Excel-Style Matrix (Monthly Columns + Single 12-Month Average Column)

[Anupambhardwaj]

Hi everyone,

I’m trying to replicate an Excel-style summary table in Power BI, and I’m struggling to get the correct layout in a Matrix visual.

What I want to achieve (same as Excel):

• Monthly columns across the matrix (Oct-24, Nov-24, Dec-24, …)

• A single column at the end showing Last 12 Months Average (excluding the latest month)

• A single column for Last 12 Months Total (excluding latest month) (optional)

• These summary columns should NOT appear after every month (only once at the end)

Excel Example (Target Layout):

Vendor Oct-24 Nov-24 Dec-24 … Sep-25 Last 12M AverageLast 12M Amount

Coding

417.75

412.25

409.25

…

431.54

456.66

5,480

Power BI always adds my measures under every month, which breaks the layout.

My Question

How can I build a Power BI Matrix that matches the Excel layout: monthly values + one single 12-month summary column at the end?

Is there a standard modeling pattern (date table, disconnected column table, bridge table, etc.) that solves this?

I would really appreciate it.

Thank you in advance!

[tharunkumarRTK · Accepted solution]

 @Anupambhardwaj 

This can be achieved with "Calculation Groups". 

Steps

1. Create your measure 

SalesMeasure = SUM(financials[Sales])

2. Create one claculation group with two items

one for actual month value :

SimpleSum = SELECTEDMEASURE()

Another one for average (You might need to adjust the average logic as per your requirement)

Average = IF(HASONEVALUE(financials[Date]), BLANK(), AVERAGEX(VALUES(financials[Date]), SELECTEDMEASURE()))

3. Place the calculation  group column and month column in "Columns" section of matrix visual 

4. Place the measure in "Values" section of your matrix visual

5. You can also add the row headers if they are any.

As you can see in the below image, both total and average columns are visible after all the months

 

 

You can download the link from here:

https://drive.google.com/file/d/10mw3f1GmHGuJQeybHEi3Su17oGCZS67l/view?usp=sharing

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[lbendlin · Accepted solution]

How can I build a Power BI Matrix that matches the Excel layout

Don't do that. Power BI is not Excel.  If you need Excel functions, use Excel.

Remember that measures are calculated twice in a table visual and four times in a matrix visual.  You can use ISINSCOPE to sense where you are in the visual and then modify the column total ( for example) to show an average instead of a sum. 

[v-pnaroju-msft]

Thankyou, @TomMartens, @tharunkumarRTK, @lbendlin and @Syndicate_Admin for your responses.

Hi Anupambhardwaj,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

We would like to inquire whether have you got the chance to check the solutions provided by @TomMartens, @tharunkumarRTK, @lbendlin and @Syndicate_Admin to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[Syndicate_Admin · Accepted solution]

If you have columns in Excel month by month, the easiest thing would be to mark all of those and unpivot them. That is, with that you would have a single column with all the dates, and the rest of the columns are repeated. After that you can distribute them back in the matrix.

I did something similar (with years) here:

https://community.fabric.microsoft.com/t5/QuickViz-Gallery/Worldwide-femicides-per-country/td-p/4882...

[lbendlin · Accepted solution]

How can I build a Power BI Matrix that matches the Excel layout

Don't do that. Power BI is not Excel.  If you need Excel functions, use Excel.

Remember that measures are calculated twice in a table visual and four times in a matrix visual.  You can use ISINSCOPE to sense where you are in the visual and then modify the column total ( for example) to show an average instead of a sum. 

[tharunkumarRTK · Accepted solution]

 @Anupambhardwaj 

This can be achieved with "Calculation Groups". 

Steps

1. Create your measure 

SalesMeasure = SUM(financials[Sales])

2. Create one claculation group with two items

one for actual month value :

SimpleSum = SELECTEDMEASURE()

Another one for average (You might need to adjust the average logic as per your requirement)

Average = IF(HASONEVALUE(financials[Date]), BLANK(), AVERAGEX(VALUES(financials[Date]), SELECTEDMEASURE()))

3. Place the calculation  group column and month column in "Columns" section of matrix visual 

4. Place the measure in "Values" section of your matrix visual

5. You can also add the row headers if they are any.

As you can see in the below image, both total and average columns are visible after all the months

 

 

You can download the link from here:

https://drive.google.com/file/d/10mw3f1GmHGuJQeybHEi3Su17oGCZS67l/view?usp=sharing

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[TomMartens]

Hey @Anupambhardwaj ,

what you are looking for is not a simple task, probably this might help tackle your challenge:

• https://community.fabric.microsoft.com/t5/Power-BI-Community-Blog/Creating-a-custom-or-hybrid-matrix...
• https://www.esbrina-ba.com/completely-arbitrary-tables-in-power-bi/

Hopefully, this will help to tackle your challenge.

Regards,
Tom