Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!