Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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
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 !
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |