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(Sales/Calls) when I apply filter context.

 

These are the results for the two models in July, being:

% CONVERSION URU =  CALCULATE(DIVIDE([Sales URU],[Calls URU]),ALLSELECTED(Uruguay[modelo]))

% AVG URU = CALCULATE(AVERAGEX(Uruguay,[% CONVERSION URU]),ALLSELECTED(Uruguay[modelo]),ALL(Uruguay[decil]))
 
The thing is that when I apply filter context and select the two models together, this i what i get:

What "% AVG URU" is doing is (0,31+0,42)/2 = 0,36 but what i expect to do is:

 

(0,95+0,71+0,63+0,34+0,2+...+0,04)/10  = 0,32

 

Thank you so much!

 

 

 

Need Help

5 Replies

  • 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!