Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |