Forum Discussion
Calculating average on year
Hi all,
as my subject seems very easy to solve, I don't succeed in this. I found already similar topics on this forum and used the formulas mentioned there, but it still doesn't give me the result I need.
I have a table with an overview of all the purchases from our business units to our suppliers, example:
date - business unit 1 - supplier 1 - item 1 - quantity
date - business unit 2 - supplier 1 - item 1 - quantity
date - business unit 1 - supplier 2 - item 2 - quantity
date - business unit 3 - supplier 2 - item 2 - quantity
and so on (you get the picture :)) .. Date can be from the 01/01/2019 until today.
I have made a visual with time dimension (month/year) on the X-as and the quantity on the Y-as.
So, if no slicer/filter is activated, I see the total sum of all the quantities purchases, par month.
If a select a specific business unit, our supplier, our item, the visual changes and shows only the quantities linked to my specific request.
Since our purchases can be very fluctuating, I want to include a 2nd number to the visual and that is the average par year.
When I use the formula already shared in other topics:
The green fields are the quantities for every month, the blue line is the average on year but you see that it's not calculated correct. (average in 2022 of 1100 for monthly purchases of 40K, 60k, ..).
So I started making 2 measures, 1 for the sum of the quantity in the year:
Sadly, that doesn't work either, as Sum Year is, once again, a fix value (no matter which business unit, supplier, ... I chose), and the Count Months gives me a also a fix result of 365 (except the year 2020, where I have 366).
Many thanks,
Best regards,
Immanuel
Did you add "avg prm" as a computed column instead of a measure?
17 Replies
You might require the following measures-
Avg of sum = Calculate (averageX(values(Purchases_All[Year]) , calculate(Sum(Purchases_All[Quantity]))), all( Purchases_All[Year]))
Avg of sum = Calculate (averageX(values(Purchases_All[Month Year]) , calculate(Sum(Purchases_All[Quantity]))), allexcept(Purchases_All, Purchases_All[Year]))
something like:
avg quantity = VAR count_months = CALCULATE(DISTINCTCOUNT(Purchases_All[Date].[Maand]),ALL(Purchases_All[Date].[Maand]) VAR count_quantity = CALCULATE(SUM(Purchases_All[Quantity]),ALL(Purchases_All[Date].[Maand]) RETURN DIVIDE(count_quantity, count_months)It isn't clear from your information if that date column is linked to a date dimension table. If it is, you would have to use the month column there in the ALL() function.
These date hyrarchies make a bit more difficult than my initial suggestions, but I just tried something similar and the below should be better. Also not that it is referencing a hidden hierachy column there: "MaandNo"; this might have a different name but hopefully the intellisense will tell you.
avg quantity = VAR count_months = CALCULATE(COUNTX(VALUES(Purchases_All[Date].[Maand]),CALCULATE(COUNTROWS(Purchases_All))),ALL(Purchases_All[Date].[Maand]),ALL(Purchases_All[Date].[MaandNo])) VAR count_quantity = CALCULATE(SUM(Purchases_All[Quantity]),ALL(Purchases_All[Date].[Maand]),ALL(Purchases_All[Date].[MaandNo])) RETURN IF(COUNTROWS(Purchases_All)>1, DIVIDE(count_quantity, count_months))With the automatically included date-hierarchy, I don't have the MaandNo, so I tried to include it (column) and then use this in the formula but it doesn't work.
When I try the formula in the 'old' version (date-table added in a seperate table & linked), my result for average is equal to the sum of all purchases in the month ...Maybe I should start again from the beginning. What is the easiest/best way to include a date-hierarchy? Automatically while loading the rapport (but this seems limited as I only have Year, Quarter, Month, Day) or by adding a independent date-table?
like I mentioned earlier, it might not be named "MaandNo". Did you try the intellisense when editing the measure?
