Measure get's filtered wrong

[Anonymous]

Hey guys,

I'm stuck on the following problem:

I created few report pages, two pages are connected with a drill through.

Now i created a measure, which should show me the total average of chemical element.

it works fine on a normal page, but on the drill through page i just get the element value which shows me on the page.

 Here is some sample data:

Table1:

Index	material	AL	C	Si
123456	steel	0,036	0,14	0,195
568745	hardend	0,038	0,14	0,205
658745	steel	0,040	0,15	0,018
986521	steel	0,032	0,16	0,087
458732	hardend	0,045	0,17	0,187
962547	steel	0,033	0,14	0,241

The Index Column is my drill through column, from one page to another.

On my main page there is some main data connected to my index column ( for now only the steel materials are connected, but the other material will follow in future, so hardcoding the material isn't a solution for me).

and on my drill through page there is more detailed data to one of the index numbers.

Now i want to have an extra field on my drill trough page where a measure is showing me the total average of the selected material.

when index 123456 is the drillthrough:

my visual on the page shows this:

Index	material	AL	C	Si
123456	steel	0,036	0,14	0,195

and now i want an extra visual where it shows me the total average of the material

which shows this:

material	AL	C	Si
steel	0,03525	0,1475	0,13525

i tried it first with hardcoding cause i dont know how to get my material dynamic, so my measure for AL looks like this:

Al Average =

Var AveAl = AVERAGEX('Table1',IF('Table1'[material] = "steel", 'Table1'[AL]))

Return

CALCULATE( AveAl, ALL('Table1'))

when i use this measure i get the following:

material	AL	C	Si
steel	0,036000	0,14000	0,195000

i tested my measure on a normal page and there it works fine, so i think there is something wrong with my measure when the drill through filter is active.

I hope some1 can understand where my problem is 

😅

[MFelix · Accepted solution]

Hi @Anonymous ,

Add the following measures to your model:

Al Average = 
CALCULATE(AVERAGE('Table'[AL]), KEEPFILTERS('Table'[material] in VALUES('Table'[material])),all('Table'))

C Average = 
CALCULATE(AVERAGE('Table'[C]), KEEPFILTERS('Table'[material] in VALUES('Table'[material])),all('Table'))

Si Average = 
CALCULATE(AVERAGE('Table'[Si]), KEEPFILTERS('Table'[material] in VALUES('Table'[material])),all('Table'))

Then use it on the drill trough:

[Anonymous]

@MFelix thanks for your help

works perfect for me 😃