Forum Discussion
How to show total as an average in matrix visualization?
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
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.
5 Replies
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] ) ) )
)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.
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?
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 :)
Anonymous
Try mine just above and tell me if it works !
