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
    2 years ago

    Did you add "avg prm" as a computed column instead of a 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
    2 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.

    • sjoerdvn's avatar
      sjoerdvn
      Icon for Solution Sage rankSolution Sage
      2 years ago

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

        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?