Calculating average on year

[Anonymous]

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:

Avg/Year = calculate (average(Purchases_All[Quantity]), allexcept(Purchases_All, Purchases_All[Year)]))

I have a number that is the same for every date in the same year but it's not correct because:

1. It doesn't change when I select other units/items/.. (the value is fix, no matter which filter/slicer is applied)

2. The numberis to low: I have monthly purchases of 40k, 50k, 20k, 60k, .. (with the lowest being 13k), however my average for that year is 1k.

I think that the formula somehow calculates = sum(quantity) in the whole year / count(quantity) in the whole year but that gives me the average purchase quantity (based on all the orders). I want to have the average purchase quantity based on the quantities par month (so something like = sum(quantity) in year / count(month) in year (can't divide fix by 12 cause I also have the data for 2023 and in that case it should divide by 9 (thats why count makes more sense)).

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:

Sum Year = calculate (sum(Purchases_All[Quantity]), ALLEXCEPT(Purchases_All, Purchases_All[Date].[Jaar]))

and 1 for the count of the months in the year:

Count Months = calculate( count(Purchases_All[Date].[Maand]), ALLEXCEPT(Purchases_All, Purchases_All[Date].[Jaar]))

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).

I can't believe that what I want to achieve is that difficult, so I guess I'm doing just some silly things.

Many thanks,
Best regards,
Immanuel

[sjoerdvn]

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))

[Anonymous]

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?

[sjoerdvn]

A proper date dimension is always recommended as it gives you more options and more control. The measure will have to change significantly however.

[sjoerdvn]

like I mentioned earlier, it might not be named "MaandNo". Did you try the intellisense when editing the measure? 

[Anonymous]

In the 'old' version with the date-table, I have the Month Number, so I changed the MaandNo to that but it isn't working.

[sjoerdvn]

no, it works quite different with a date dimension. I have created a smilar model, so the column names are different, but this works:

avg prm = 
VAR count_months  = CALCULATE(DISTINCTCOUNT('date'[nr_month])
        ,CALCULATETABLE(sales,ALL(),VALUES('date'[nr_year])) 
        ,CROSSFILTER('date'[dt_date],sales[dt_mutation],Both))
VAR count_quantity  = CALCULATE(SUM(sales[amt_ann_prm_net]),ALL('date'),VALUES('date'[nr_year]))
RETURN IF(COUNTROWS(sales)>1,  DIVIDE(count_quantity, count_months))

[Anonymous]

Hi sjoerdvn,

can you share your model, so I can compare with mine and see where it goes wrong? I used your formula, adjusted the column names to the 'right' ones but still doen't have what I need ..

Thanks.

[sjoerdvn]

Discovered a bug in my example, so changed my measure like below.

avg prm = 
VAR count_months  = CALCULATE(DISTINCTCOUNT('date'[nr_month])
        ,CALCULATETABLE(sales,ALL(),VALUES('date'[nr_year])) 
        ,CROSSFILTER('date'[dt_date],sales[dt_mutation],Both))
VAR count_quantity  = CALCULATE(SUM(sales[amt_ann_prm_net]),ALL('date'),VALUES('date'[nr_year]))
RETURN IF(COUNTROWS(sales)>0,  DIVIDE(count_quantity, count_months))

[Anonymous]

It still doesn't work, although I'm following your instructions 🙂

Formula used:

avg prm =

VAR count_months  = CALCULATE(DISTINCTCOUNT('Date'[Month Number])

        ,CALCULATETABLE(Purchases_All,ALL(),VALUES('Date'[Year]))

        ,CROSSFILTER('Date'[Date],Purchases_All[Date],Both))

VAR count_quantity  = CALCULATE(SUM(Purchases_All[Quantity]),ALL('Date'),VALUES('Date'[Year]))

RETURN IF(COUNTROWS(Purchases_All)>0,  DIVIDE(count_quantity, count_months))

Relations between the tables:

Report:

I have the feeling that it has to do with something in my date-table, no?

[sjoerdvn · Accepted solution]

Did you add "avg prm" as a computed column instead of a measure? 

[Anonymous]

Hallelujah, it works 🙂

[sjoerdvn]

Hard to see without the data. Also I don't get the order in your month axis. it is neither sorted by month name, month number or any of the amounts?
Your "Month" column  should be sorted by your "Month number" column.
Also, for simplicity, maybe test first with just the total year amount.
 

total year quantity = CALCULATE(SUM(Purchases_All[Quantity]),ALL('Date'),VALUES('Date'[Year]))

[sjoerdvn]

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)

[Anonymous]

Thanks for your reply.
I've tested this function but the numbers are the same as the ones for quantity:

[sjoerdvn]

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.

[Anonymous]

There is no date dimension table involved. The data is loaded from a database and has only a date-column. When loading the data, automatically a date-hierarchy is included.

In a first attempt I was working with a date-table. As a asking a colleague from help, he told me that he always works with the automatically added date-hierarchy, so thats why I tried to do it like that.

I still have this 'old' version of my report, I will check if I get other/better results when using the formula there.
*Update* doesn't work, i get a 'syntax for Month is incorrect'-error ...

Set-up from the 'old' file:

[nirali_arora]

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]))