Filter table at higher level of grain from a graph

[RK_JOB]

Hello,

Is it possible to show the higher level data and not filter all the way through?

For e.g.: Here if someone clicks on the 80.7 in the graph, then in the table below instead of showing just that row, show everything (like in figure 2).

Figure 1:

Figure 2:

Users don’t want to click on label, they want to click on the bar, but still want to see everything related to that Manager Level data.

Can we achieve this with DAX? I put in the sample file here:

https://drive.google.com/file/d/1RBMJfF5BVcxutHr-jCU7RgSP1IQEboxO/view?usp=sharing

Thanks. Please let me know if you have any questions.
RK

[Anonymous]

@RK_JOB I believe we are on the right track now, although I'm still trying to get it to filter out groups with 0 head count, but that could be done in the visual as well. Also, i'm, noticing small changes in the numbers when clicking on different colors in a single bar. That shouldn't be happening though, right?

Good news, this also works with multiselections and the labels as well.

To get the calc group in play, add it as a filter to the table

[Anonymous]

Striking this out since it didn't work

Ok, so I created  a Calculated table summarized by Manager Type and Ethnic group.  This is pretty quick and dirty but hopefully it gets you on a good path.

RollupTable = SUMMARIZECOLUMNS(
'HC'[GLMNGR_TEXT],'HC'[ETHNIC_GRP_TEXT],
"HeadcountRollup",[Headcount]
)

Also added a relationship (this one is many to many)

Switch the detail table over to use the Summary table insted

There's going to be other options to handle this but this is the one I went to first after downloading your sample file... Which BTW is awesome of you to put together.

[RK_JOB]

@Anonymous Thanks for the idea. But why your numbers are little inflated? I modified the table a little bit and put filter below. It has everything + filter for Country which is United States. But in the rollup table I am still getting little bit more number than what I should be getting.

Any idea?

[Anonymous]

I was racking my brain on this quite a while last night.  I think Calculation group is the right way to go, and I would totally expect this to work, but still getting the selected Ethnic group instead of all of them.  

The nice thing about getting it to work as a Calculation group is that it's scalable across all your measures.  I'm not sure why the

ALL ( 'HC'[ETHNIC_GRP_TEXT] ),

isn't breaking the filter scope though.

my guess is it has to do with everything being on the same table but that still seems odd to me.  I have another idea I'm working on now, but wanted to share this for purpose of conversation and noting failed attempts for reference.

[RK_JOB · Accepted solution]

@Anonymous Really appreciate your effort. I got a solution. Someone else suggested this on other forum. If you have any other idea then I would love to know another way of doing this. Nonetheless, here it is with disconnected table and measure. I don't understand it fully yet but it but it works. 🙂

Step 1: 

Table = ALL(HC[ETHNIC_GRP_TEXT])

Step 2:

Measure 2 =

VAR SV =

SELECTEDVALUE ( 'Table'[ETHNIC_GRP_TEXT] )

VAR SM =

SELECTEDVALUE ( HC[GLMNGR_TEXT] )

RETURN

IF (

ISFILTERED ( HC[ETHNIC_GRP_TEXT] ),

CALCULATE ( [Headcount], HC[GLMNGR_TEXT] = SM && HC[ETHNIC_GRP_TEXT] = SV ),

CALCULATE ( [Headcount], HC[ETHNIC_GRP_TEXT] = SV )

)

Step 3:

Final =

IF (

SUMX ( VALUES ( 'Table'[ETHNIC_GRP_TEXT] ), [Measure 2] ) = 0,

BLANK (),

SUMX ( VALUES ( 'Table'[ETHNIC_GRP_TEXT] ), [Measure 2] )

)

[Anonymous]

@RK_JOB did you see my reply regarding the Calculation Groups.  The nice thing with that option is it will work for other measures as well.  I can dig into it some more if you are interested in that option.  I'll play with the other solution you found as well and compare the two

[Ritesh_Air]

Tbh, I googled calculation groups and tried to read upon how they work. Still learning. 🙂

[Anonymous]

Off the top of my head it's probably the Many-Many relationship causing the problem. I'll keep playing with it, but someone else might beat me to it.

[Anonymous]

Haven't looked at your sample file but I will. Until then, if you want to persist the manager level in scope you could do a measure that use ALLEXECEPT() and use that measure in your table.

CALCULATE([Measure],ALLEXCEPT('ManagerLevel'))

[RK_JOB]

@Anonymous 

No, didn't work.

Manager All Except = CALCULATE([Headcount],ALLEXCEPT(HC,HC[GLMNGR_TEXT]))

This when I didn't select anything.

This is, when I selected 80.7 in the bar:

I don't think measure is the way to go...may be I don't know.

Basically, I want even if they select the bar which is 80.7% (which mean it's Executive and Ethnicity is White), in the table I want to show all the ethnicities for the Executive.

I hope it makes sense.

Thanks,

RK