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

 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!

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. 

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/4882912