Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Undo filter in Drillthrough

Hey guys,

 

so I created this drillthrough system:

 

 

And now, in the DrillDown of a certain Project, I want to be able to compare it to other projects (which are obviously filtered out).
I already understood to use the ALL & SUMX function to make a measure for the average Margin  of all projects:

 

> Average Margin = SUMX(ALL(ProjectData), ProjectData[Margin]))


This works fine.
But what I want is a column that has all the other projects(rows) unfiltered. With this I would like to make a clustered column chart to see this drilldown project in comparison with others.

Is there a way?

Thanks guys,


Greetings,


Janik
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  Anonymous 

    you could try this way as below:

    Step1:

    Create a separate project table and create a relationship as below:

    Note: The cross filter direction must be Single and is separate project (1:*) ProjectData

    Separate Project = VALUES('ProjectData'[Project])

     

    Step2:

    Create measure like this:

    Average Margin = SUMX(ALL(ProjectData), ProjectData[Margin])
    Basic Margin = CALCULATE(SUM(ProjectData[Margin]),FILTER(ALL(ProjectData),ProjectData[Project]=MAX('Separate Project'[Project])))

    And drag them into a vsiual with project field from the separate project table

     

    Result:

    Simple example, I drillthrough by USA

     

     

    and here is my sample pbix file, please try it.

     

    Regards,

    Lin

2 Replies