Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 resul...
  • sjoerdvn's avatar
    sjoerdvn
    3 years ago

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

sjoerdvn's avatar
sjoerdvn
Icon for Solution Sage rankSolution Sage
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    sjoerdvn
    Icon for Solution Sage rankSolution Sage
    3 years ago

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

     



    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        sjoerdvn
        Icon for Solution Sage rankSolution Sage
        3 years ago

        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))
  • sjoerdvn's avatar
    sjoerdvn
    Icon for Solution Sage rankSolution Sage
    3 years ago

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