cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kienlow Visitor
Visitor

Dynamic Measure that uses clusters - help!

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

Sample Image.png 

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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Dynamic Measure that uses clusters - help!

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

1 REPLY 1
Community Support Team
Community Support Team

Re: Dynamic Measure that uses clusters - help!

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