Re: Calculate Average with Filter Context

tomasbaleriani · ‎09-19-2022

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!

tomasbaleriani · ‎10-03-2022

Model 1

Model 2

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

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 · ‎10-03-2022

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

tomasbaleriani · ‎10-04-2022

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

With your measure, what i get is this

[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 · ‎10-04-2022

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

lbendlin · ‎09-20-2022

Calculate Average with Filter Context