Forum Discussion

tomasbaleriani's avatar
tomasbaleriani
Frequent Visitor
3 years ago

Calculate Average with Filter Context

Hi, i have a problem that i can´t solve.   I have the results of machine learning's models, divided by month and decil.   What i am trying to do is to calculate the average conversion rate(...
Need Help
tomasbaleriani's avatar
tomasbaleriani
Frequent Visitor
3 years ago

Model 1

DECILCALLSSALES

SALES/CALLS

10

784965280.67%
9764283060.4%
8   
7   
6.........
5.........
4   
3   
2.........
1.........

 

Model 2

DECILCALLSSALESSALES / CALLS
10168071210.72%
916500910.55%
816233850.52%
7.........
6.........
5.........
4.........
3.........
2...  
1   

 

I have created a data segmentation that filter Model 1 and Model 2 and these are the result

 

DECILCALLSSALESSALES / CALLS
1095303 (78496+16807)649 (528+121)0.68%
9929283990.43%
8.........
7.........
6.........
5   
4   
3   
2   
1.........

 

I have created the next measure:

[AVG SALES/CALLS] = 

    CALCULATE(
    AVERAGEX(TABLE,DIVIDE(SALES.CALLS))
 
What i get is the average of the two models... ((0.67%+0.4%+...)/10+(0.72%+0.55%...)/10))/2
 
But i want to get is the average of the last table
(0.68%+0.43%+....)/10
 
Thank you so much!
 
 
 
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
3 years ago

[AVG SALES/CALLS] = DIVIDE(SUMX(TABLE,SALES),SUMX(TABLE,CALLS),0)

  • tomasbaleriani's avatar
    tomasbaleriani
    Frequent Visitor
    3 years ago

    Thank you for your response. However, I´m not getting the desired result.

    With your measure, what i get is this

    DECILCALLSSALESSALES/CALLS

    10

    		95303(78496+16807)649(528+121)0.68%
    9929283990.43%
    8899163600.4%
    7857532330.27%
    6793701990.25%
    5714241880.26%
    417771680.38%
    320885690.33%
    222536650.29%
    124100510.21%

     

    [AVG SALES/CALLS] = (649+399+...)/(95303+...24100) = 0.38%

    What i want to get...

     

    [AVG SALES/CALLS] = (0.68%+0.43%+0.4%+...+0.21%)/10 = 0.35%

     

    Thank you!

     

     

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      3 years ago

      [AVG SALES/CALLS] = AVERAGEX(DIVIDE(SUM(TABLE[SALES]),SUM(TABLE[CALLS]),0)