Monthly Average Orders per Customer in Totals in Matrix Table

[etane]

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!

[FBergamaschi · Accepted solution]

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

[sanalytics]

@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

[etane]

Hi @sanalytics .

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

[FBergamaschi]

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

[MasonMA]

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

[MasonMA · Accepted solution]

@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:

[etane]

Thanks!  This works.

[FBergamaschi · Accepted solution]

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

[etane]

@FBergamaschi  Thanks.  Short, simple and works.

[FBergamaschi]

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

[etane]

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

[techies]

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.

[etane]

@techies yeah at least in excel you can this: