Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tomasbaleriani
Frequent Visitor

Total Average with Filter Context

Hi, I´m having problems to calculate a total average when I use filter context.

 

I have this TABLE 1 :

I need to group X and Y, because they are the same ml Model, so i ve created the ML GROUPED Column

 

MonthML MODEL     ML GROUPEDDECILCALLSSALESSALES/CALLS
JANXPortin1   
JANXPortin2   
JANXPortin3   
JANXPortin...   
JANXPortin10   
JANYPortin1   
JANYPortin2   
......Portin...   
JANYPortin10   

 

I have this measure :

LIFT = [SALES/CALLS DECIL 10] / [AVG SALES/CALLS]

[SALES/CALLS DECIL 10] =  CALCULATE([SALES/CALLS], TABLE1[decil] IN { 10 }, ALLSELECTED((TABLE1[ML MODEL])))

[AVG SALES/CALLS] = 

CALCULATE(
    AVERAGEX(TABLE1,[SALES/CALLS]), ALLSELECTED(TABLE1[ML MODEL]))

 

Auxiliary Measures:

[SALES/CALLS] = [SALES] / [CALLS]

[SALES] = SUM(TABLE1[SALES])

[CALLS] = SUM(TABLE1[CALLS])

 

When i filter MODEL = 'X' i get this results:

 

DECILCALLSSALES SALES/CALLS

1

306113,59%
2498153,01%
3755182,38%
4945171,8%
5555961390,25%
6637651340,21%
7..........
8   
9   
10784965280,67%

 

 

When i filter MODEL = 'Y' i get this results:

 

DECILCALLSSALES SALES/CALLS

1

23794400,17%
222038500,23%
320130510,25%
416826510,3%
555596490,31%
663765650,42%
7..........
8   
9   
10168071210,72%

 

My goal is to apply filter context, ML grouped = 'Portin' and see something like this

 

DECILCALLSSALES SALES/CALLSSALES/CALLS DECIL 10AVG SALES/CALLS

1

(306+23794)(11+40)0,21%0,68% 
2(498+22038)(15+50)0,29%0,68% 
3(755+20130)(18+51)0,33%... 
4   ... 
5     
6     
7..........  
8     
9     
10(78496+16807)(528+121)0,68%0,68% 

TOTAL                                                                                                                                  PROBLEM

 

What i want is that the total for AVG SALES/CALLS be calculated as:

 

AVG SALES/CALLS = (0,21% + 0,29 % + 0,33% + ... + 0,68%) = 0,35%

 

What I´m actually getting is 

 

AVG SALES/CALLS = ((3,59% + 3,01% + ... + 0,67%)/10 + (0,17% + 0,23% +...+0,72%)/10)/2

 

2 REPLIES 2
tomasbaleriani
Frequent Visitor

Hi  amitchandak, thank you for the answer. I tried your solution but i´m still getting a mistake.

 

tomasbaleriani_1-1666016848260.png

For example the first row which has 1,88% for AVG SALES/CALLS is calculated as

(3,59% + 0,17%)/2 = 1,88% Which the AVG SALES/CALLS for model X and model Y

 

 

 

 

 

 

amitchandak
Super User
Super User

@tomasbaleriani , If you want a simple avg of Avg (the sum of the divide)

 

You need measure like

 

AverageX(Values(Table1[ID]), calculate(divide( [SALES] , [CALLS]) ) )

 

where [SALES] and  [CALLS] are measures

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.