Filter and measure that look at entire project.

[sjrrkb123]

So I have the following example dataset

Project	revenue	margin	margin %	work type		Practice	Work Type
A	100	99	99%	1a		1	1a
A	100	1	1%	1a		1	1b
A	50	50	100%	2b		1	1c
B	33	22	67%	1a		2	2a
B	90	89	99%	1a		2	2b
B	100	10	10%	1b		2	2c
B	40	11	28%	2c

Where the project table and worktype tables are separate. They are linked by the key called work type. I am running into a problem where I filter projects for a specific practice, it filters the resulting table for the parts of the projects that fit that specific practice, rather than the entire project. So if I filter for practice 1, it only shows the portion of project A that relates to practice 1 rather than the entire project. Is there any way to set it up such that this region slicer will show all projects that contain a specific practice rather than the portions of that project that are in the given practice?

Secondly, and related to this problem, I am trying to find projects who, in total margin %, are <10% or >90%. However, due to this setup, it only looks line by line and sees projects whose margin is <10% or >90% within a given worktype. So when I set up a filter for <10% or >90%, it only filters for parts of the project whose margin fit those characteristics. I tried a calculation for this but it is not working...

here is what I tried: 

Percentage Bins =

var margin = CALCULATE(SUM('Projects'[Margin($$)]),

     FILTER(CALCULATETABLE('Projects',

     ALL('Projects'[Revenue]),

    ALL('Projects'[Margin($$)])),

    'Projects'[ProjectsKey] <> BLANK()))

var rev = CALCULATE(SUM('Projects'[Revenue]),

     FILTER(CALCULATETABLE('Projects',

     ALL('Projects'[Revenue]),

     ALL('Projects'[Margin($$)])),

    ' Projects'[ProjectsKey] <> BLANK()))

var m_percent = DIVIDE(margin, rev)

RETURN

SWITCH(TRUE(),

m_percent <.10, "< 10",

m_percent >= .10 && m_percent <=.9, "10 - 89.9999",

m_percent > .9,"> 90")



Note, this has to be done as a column (at least I think so) because I need to be able to filter in the context of >90% or <10% and I cannot filter by a measure. 

[parry2k]

@sjrrkb123 your first is not clear, if you are filtering the project, it should show all the work types from the tables? Can you share how you are using it? Share the relationship diagram.

[sjrrkb123]

@parry2k Sorry if I was not clear. I have updated the description but I will explain further here.

A given project can have 1 or many practices. Each practice can have 1 or many work types. So on a given project, there may be many practices each with multiple work types. I have a slicer for practices that, in its current form, filters the table visual in a manner I do not want. When I select Practice 1 and look at project A, I only see the first two line items as they are relating to practice 1. I want the practice slicer to highlight projects that have practice 1 but still display the entirety of the project. 

So basically what happens after I hit Practice 1 in the slicer, the only thing I see in the visual is the following rows.

A	100	99	99%	1a
A	100	1	1%	1a

Is there any way to make the slicer show the entirety of the project, rather than just the portions that are in practice 1. 

[v-yuta-msft · Accepted solution]

@sjrrkb123 ,

I'm afriad this couldn't be achieved, slicer will filter other visuals if you don't disable the interaction between slicer and other visuals. And slicer also doesn't have such feature like highlighting the filtered rows while keep the entire data.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.