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? 

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

        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.