Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi I can't seem to figure out how to calculate this. I have the following table. the automatic AVG measure by powerbi is not what i want as it averages the single transaction appening in a month. I want to know the average for the year.
ideally that would be:
(total amount of the year)/(count of month in the year)
but I cannot create the correct measure.
@SimoneXasto This should work in something like a card visual:
Measure = DIVIDE( SUM('Table'[Amount]), 12 )
Or, if you want it to be cumulative you could do this:
Measure =
VAR __Date = MAX('Table'[Date])
VAR __Table =
SUMMARIZE(
FILTER( 'Table', [Date] <= __Date ),
[Month],
"__Amount", SUM( 'Table'[Amount] )
)
VAR __Result = DIVIDE( SUMX( __Table, [__Amount] ), COUNTROWS( __Table ) )
RETURN
__Result
Hi @Greg_Deckler thanks for the reply I actually used a different method but I still need a bit of help.
Let me specify more things about my data set.
I have transactions (categorized and sub-categorized) spread across different years. I want to achieve two things:
1. to have the AVG amount of the transaction sums per each year
2. the same as above but divided by categories
So far I have been able to create static tables and measures filtering what I want with the two following formulas:
Table = SUMMARIZE(
FILTER(Merged_new,[Year]=2022 && [Type]="Expenses"),
Merged_new[Year],
Merged_new[Month],
Merged_new[Type],
"Sum_Net_Amount",SUM(Merged_new[Net Amount])
)
Measure =
AVERAGEX(
ALL('Table'[Month]),
CALCULATE(SUM('Table'[Sum_Net_Amount]))
)
This is very tedious and manual work obviously. How could I improve this and make it more dynamic? Any input will be highly appreciated.
If needed I can also provide the pwbi file.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |