Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Measure get's filtered wrong

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:

IndexmaterialALCSi
123456steel0,0360,140,195
568745hardend0,0380,140,205
658745steel0,0400,150,018
986521steel0,0320,160,087
458732hardend0,0450,170,187
962547steel0,0330,140,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:

 

IndexmaterialALCSi
123456steel0,0360,140,195

 

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

which shows this:

materialALCSi
steel0,035250,14750,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:

 

materialALCSi
steel0,0360000,140000,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 

😅

 

 

 

 

Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    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:

     

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      MFelix thanks for your help

      works perfect for me ğŸ˜ƒ