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 have a dashboard with locations on a map and a table showing market share (i.e. sum of Sales for one Company divided by sum of Sales for all Companies), plus some other filters and things. A simplified example is shared here. I would like market share measures to be correctly recalculated when the lasso / rectangle select option is used and can't figure out how to get it to work. I need both the numerator and denominator to be responsive to the map selections, but the denominator to ignore the "Company" field.
When no selections on the map are made, it works correctly:
When selections on the map are made, the numerator for market share works but the denominator takes into account all locations:
Where (in the example above) it should be 80% = 1064 / (1064 + 266) and 20% = 266 / (1064 + 266).
I'm not the best with measures, but this is what I have so far:
I'm guessing this is some trick with ALL / ALLEXCEPT to get the measure to respond to criteria correctly. Any help is greatly appreciated! Again here is my fake / simplified file.
Solved! Go to Solution.
@ELW,
Try this measure. It uses ALLSELECTED which uses the filter context from outside the visual.
Market Share =
DIVIDE (
SUM ( 'Sample Data'[Sales] ),
CALCULATE ( SUM ( 'Sample Data'[Sales] ), ALLSELECTED ( 'Sample Data' ) )
)
Proud to be a Super User!
That got it!! Thank you so much!
I need to learn when to use all / allexcept / allselected... I do not understand the nuance.
@ELW,
Try this measure. It uses ALLSELECTED which uses the filter context from outside the visual.
Market Share =
DIVIDE (
SUM ( 'Sample Data'[Sales] ),
CALCULATE ( SUM ( 'Sample Data'[Sales] ), ALLSELECTED ( 'Sample Data' ) )
)
Proud to be a Super User!
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 |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |