Dynamic Measure that uses clusters - help!

[Anonymous]

Measure wanted:

I have a problem that my team and I are having difficulty resolving. It involves creating a measure that will need to be dynamic depending on the selection made.

The data is broken down by a number of different categories, these being Year, Gender, Nationality, Building and Group along with a value which represents the number of people in that grouping.

The measure we want to create can be broken down into a few smaller ones.

S = Sum of [# People]   - This is the number of people in that category.

M = Sum of ALLSELECTED[# People] – This is the number of people overall.

P = S/M 

X = P*(1-P)

Ratio : Sum of X

The first issue that we are having is that the Ratio is not adding up. Having done some research, I believe I need to use HAVEONEVALUE, however I have not been able to get this working.

The second and more complex issue we are having is that we need this measure to be dynamic. I believe this is mostly because calculating M is not working as we want.

Example:

If I only want to see this Ratio Measure by Year, Building then the measure needs to work out the Ratio for each Nationality based upon M being the number of students in that Building for that year.  The below table is the expected results

 

What we will ultimately be wanting to plot on the visual is

Year	Building	Ratio
2016	Building 1	0.66
2016	Building 2	0.60
2017	Building 1	0.65
2017	Building 2	0.65

This measure will also be used to split the data differently in different visuals, eg. Ratio by Group over Years.

Attached is a Pbix file with data. Thanks so much for your help in advance!

[v-yuta-msft · Accepted solution]

Hi kienlow,

So your requirement is to create a measure which can remove some columns in your table chart based on the selection in your slicer, right? I'm afraid power bi doesn't support this feature because slicer and measure are all based on rows which can only filter rows not columns.

Regards,

Jimmy Tao