Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Measure Average

Hello,   I am calculating Sales Rate using the formula- Sum of Table1[Amt]/ Sum of Table2[Amt] If either of the amt is zero, I have to show blank which is what I am doing using the current measure...
Need Help
v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

Anonymous

 

According to your description, you want to calculate average baesd on the result of a calculated measure on row level. Right?

Since your calculation need to be evaluated on each row, you should put your calculated measure expression into AVERAGEX() function.

 

Sales Rate = AVERAGEX('Table',IFERROR([Measure 1]/[Measure 2],BLANK()))

 

Regards, 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi v-sihou-msft

     

    Thanks for replying. What is happening in this current context is, If I select 2016 and 2017 in my year slicer for the month of Jan, it is calculating Sum of (Table1)Amount for Jan 2016 + Sum of (Table1)Amount for Jan 2017/Sum of (Table2)Amount for Jan 2016+ Sum of (Table2) Amount for Jan 2017.

     

    What I would like is Rate1= Sum of (Table1)Amount for Jan 2016/ Sum of (Table2)Amount for Jan 2016

    Rate 2= Sum of (Table1)Amount for Jan 2017/ Sum of (Table2)Amount for Jan 2017

    then Final Rate= (Rate 1+ Rate 2)/2

     

    I tried using the solution that you provided, but I am confused what to use in "Table". I used Date table since it is given relationship with both Tables. Not getting desired result.

     

    Thanks in advance.