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.
I need to compare various measures within a population, where it's one small group vs the rest of the population as in the chart below. This is easy enough to do by defining a measure (e.g. custom client group vs rest of population = if(client[client number] in {"5155","0734"},"custom client group","rest of population")) and then using that measure on the axis.
However, I need the custom group to be user-definable at runtime. This is easy enough to do in Desktop since the user could just modify the measure, but this is not possible in app.powerbi.com where the report ultimately will be accessed.
Is there any mechanism or add-in that could be used to let the user select what values are in the custom group? I envision something like a slicer that doesn't filter data, but rather binary-selects which group to put the selected value in.
Any ideas? I'm really hoping this is possible!
Hi @scotjn1
I think I know what you are after, and yes it is possible.
I've made a very basic PBIX file here that demonstrates the technique.
https://1drv.ms/u/s!AtDlC2rep7a-oiuZmLqvGVA40a56
But in essence, you need a measure to sum up the selected values such as
Selected = SUM(Table1[Value])
Then have another measure to sum up the entire set, but subtract the above measure
Not Selected = SUMX( ALL('Table1'), 'Table1'[Value] ) - [Selected]
Hi Phil,
Thanks for the reply. This is in essence what I'm after. For simple SUMs and AVERAGEs, it seems to work very well.
Avg Selected = AVERAGE(Table1[Value]) Avg Not Selected = (SUMX(ALL(Table1),Table1[Value]) - SUM(Table1[Value])) / (COUNTX(ALL(Table1),Table1[Value]) - COUNT(Table1[Value]))
However, I'm hoping not to have to add dozens of additional measures to get to the "not selected" scenario for each of the ones already in the model. Especially given that some of them are pretty complex as it is.
I'm still optimistic that there is a simple solution to making a single dimension perform this way against multiple measures on the fly. If no ideal solution presents itself, I'll mark yours as the best one.
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 |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |