Average of 12 months, average of prior 12 months and sum at the bottom in matrix

[sheebharani]

Hi All,

I am trying to get average of 12 months (excluding the current month),  average of prior 12 months and sum at the bottom. Also the cell vales have to be sum and not average.

I have current month and -13 months as column with customer name on rows in the display and not prior 12 months(i.e not complete 25 months - current month -12 -12).

If i add average measure it will repeat for every month.

I do not want that, i wnat to just show averages at the end and sum at the bottom.

Customer Name	2024-04	2024-03	2024-02	2024-01	2023-12	2023-11	2023-10	2023-09	2023-08	2023-07	2023-06	2023-05	2023-04	Average of 12 momths	Average of prior 12 months
ABC			10										15	2.08	3
XYZ	-30	10	20	30	35	40	45	50	55	60	70	80	90	48.75	50
Total	-30	10	30	30	35	40	45	50	55	60	70	80	105	50.83	53

[Anonymous · Accepted solution]

Thanks @Greg_Deckler 

Hi, @sheebharani 

Based on your description, a similar matrix visual was created using the following sample data:

I use the following DAX expression in the matrix:

EachMonth = SUM('Table'[value])

Totals =
IF (
    ISINSCOPE ( 'Table'[Date] ),
    SUM ( 'Table'[value] ),
    DIVIDE ( SUM ( 'Table'[value] ), 12 )
)

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@sheebharani Hard to know for sure, but seems like you need a variety of things here. 

First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Also, you will need Custom Matrix Hierarchy: The New Hotness (Custom Matrix Hierarchy) - Microsoft Fabric Community

And probably Better Rolling Average: Better Rolling Average - Microsoft Fabric Community