cancel
Showing results 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

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

 Month ML MODEL ML GROUPED DECIL CALLS SALES SALES/CALLS JAN X Portin 1 JAN X Portin 2 JAN X Portin 3 JAN X Portin ... JAN X Portin 10 JAN Y Portin 1 JAN Y Portin 2 ... ... Portin ... JAN Y Portin 10

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:

 DECIL CALLS SALES SALES/CALLS 1 306 11 3,59% 2 498 15 3,01% 3 755 18 2,38% 4 945 17 1,8% 5 55596 139 0,25% 6 63765 134 0,21% 7 .... ... ... 8 9 10 78496 528 0,67%

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

 DECIL CALLS SALES SALES/CALLS 1 23794 40 0,17% 2 22038 50 0,23% 3 20130 51 0,25% 4 16826 51 0,3% 5 55596 49 0,31% 6 63765 65 0,42% 7 .... ... ... 8 9 10 16807 121 0,72%

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

 DECIL CALLS SALES SALES/CALLS SALES/CALLS DECIL 10 AVG 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
Frequent Visitor

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

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

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

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors