How to show total as an average in matrix visualization?

[Anonymous]

Hello All,

I need to show sum of loan amount for a particular month and then display the average loan amount for a quarter. If I fetch loan amount from My table and then use power bi to average it, then my row values also get averaged. I want only my quarter total to be average and row values to be summed. How do I achieve that? Please see the screenshot for better clarity.

On selecting Sum

 On selecting Average

[hohlick]

Hi @Anonymous

You need to create a measure for this calculations and put it into values of matrix.

Measure will looks like this (depending on your data model and table/columns names):

Calc =
IF (
    HASONEVALUE ( 'DatesTable'[Month] ),
    CALCULATE ( SUM ( 'FactTable'[Amount] ) ),
    AVERAGEX ( 'FactTable', CALCULATE ( SUM ( 'FactTable'[Amount] ) ) )
)

[Anonymous]

Hi @hohlick

I tried your solution and I am getting the following error-

The following syntax error occurred during parsing: Invalid token, Line 2, Offset 3,  .

Thanks in advance 🙂

[Datatouille]

 @Anonymous

Try mine just above and tell me if it works !

[Datatouille · Accepted solution]

HI @hohlick @Anonymous,

Supposing you have a Calendar or Date table dedicated to Time Intelligence.

[LoanMeasure] = AverageX( Values(Calendar[Month] ) ,  Calculate ( Sum( 'Fact'[LoanAmount] ) ) ) also works and doesn't need any IF statement.

Even better, use interim measure:

[SumOfLoanAmount] = Sum( 'Fact'[LoanAmount] )

And use this measure:

[LoanMeasure] = AverageX( Values(Calendar[Month] ) ,  [SumOfLoanAmount] )

A measure automatically wraps a "Calculate" which activates context transition.

[Anonymous]

Hi @Datatouille

Your solution worked well and now I am able to get quarter average.Thanks a lot for that. I do have a different query now. I am displaying data for three days for the month of July and August as seen in the screenshot.

Even though for a quarter it is showing average , thanks to your solution. It is summing up the data for the three days. I would like average for that too. How can that be achieved?