topic Monthly Average Orders per Customer in Totals in Matrix Table in DAX Commands and Tips

Monthly Average Orders per Customer in Totals in Matrix Table

etane — Sat, 12 Jul 2025 01:03:13 GMT

Hello.

I have a matrix table with Orders per Customer by month below:

I want to be able to show the average instead of the total in the colum total but not in the row total. Not sure whether this is possible. Below is the desired result which is Orders per Customer divided by Month(today()):

Please help. Link to WIP file here: Link

Thanks!

Re: Monthly Average Orders per Customer in Totals in Matrix Table

techies — Sat, 12 Jul 2025 06:51:33 GMT

Hi etane i guess there is no built-in setting to override column totals to show an average while keeping row totals as a sum.

Re: Monthly Average Orders per Customer in Totals in Matrix Table

FBergamaschi — Sat, 12 Jul 2025 08:46:22 GMT

Create a new measure like this

New measure=

IF ( ISINSCOPE( 'Calendar'[Year Month] ), [Orders per Customer],[Orders per Customer AVG] )

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Monthly Average Orders per Customer in Totals in Matrix Table

MasonMA — Sat, 12 Jul 2025 16:12:37 GMT

etane

Hi, your MONTH(TODAY()) returns the current month number and It assumes that the number of months in your matrix equals the current month. What if there are filtered months?

I would use,

Orders per Customer AVG = VAR MonthCount = CALCULATE( DISTINCTCOUNT('Calendar'[Year Month]), REMOVEFILTERS('Customer Table'[New or Existing]) ) RETURN DIVIDE([Orders per Customer], MonthCount)

and

OrdersPerCustomer_Adj = IF( ISINSCOPE('Calendar'[Year Month]), [Orders per Customer], [Orders per Customer AVG] )

Your 'Orders per Customer AVG' as below:

and Orders Per Customer:

Re: Monthly Average Orders per Customer in Totals in Matrix Table

sanalytics — Sat, 12 Jul 2025 15:15:39 GMT

etane You want to show average in column total not in row total.. Like below ?

Attached is the pbix file for your reference.

Regards,

sanalytics

Re: Monthly Average Orders per Customer in Totals in Matrix Table

etane — Mon, 14 Jul 2025 16:10:41 GMT

Hi sanalytics .

Very close. I am hoping the entire Column Total shows average instead of sum (including the .35 as below):

Re: Monthly Average Orders per Customer in Totals in Matrix Table

etane — Mon, 14 Jul 2025 16:14:13 GMT

techies yeah at least in excel you can this:

Re: Monthly Average Orders per Customer in Totals in Matrix Table

FBergamaschi — Mon, 14 Jul 2025 16:18:23 GMT

sanalytics version =

IF ( ISINSCOPE( 'Calendar'[Year Month] ), [Orders per Customer],[Orders per Customer AVG] )

If you use this code, it works (already sent yesterday, here again for convenience)

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Monthly Average Orders per Customer in Totals in Matrix Table

MasonMA — Mon, 14 Jul 2025 16:27:44 GMT

FBergamaschi This should work if the user is not considering replacing the Month Count part.

Re: Monthly Average Orders per Customer in Totals in Matrix Table

etane — Mon, 14 Jul 2025 16:37:37 GMT

FBergamaschi Thanks. Short, simple and works.

Re: Monthly Average Orders per Customer in Totals in Matrix Table

etane — Mon, 14 Jul 2025 16:40:41 GMT

Thanks! This works.

Re: Monthly Average Orders per Customer in Totals in Matrix Table

FBergamaschi — Mon, 14 Jul 2025 16:43:43 GMT

Thanks, in this case please mark it as a solution, I think you marked a different post and not mine (maybe your reply to my answer). Not referring to other solutions you accepted (it is OK to accept more than 1), just to the post you marked as a solution

Thanks

Re: Monthly Average Orders per Customer in Totals in Matrix Table

etane — Mon, 14 Jul 2025 16:45:45 GMT

Yup I clicked the wrong post. Thanks again for your help.