Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
ELW
Advocate II
Advocate II

Measures Partially Responsive to Map Lasso Select

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:

Market Share 1.PNG

 

When selections on the map are made, the numerator for market share works but the denominator takes into account all locations:

ELW_0-1703709422023.png

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:

Market Share = DIVIDE(
    SUM('Sample Data'[Sales]),
    CALCULATE(
    SUM('Sample Data'[Sales]),
       ALLEXCEPT('Sample Data','Sample Data'[Criteria])))

 

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.  

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@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' ) )
)

 

DataInsights_0-1703715429179.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
ELW
Advocate II
Advocate II

That got it!!  Thank you so much!  

 

I need to learn when to use all / allexcept / allselected...  I do not understand the nuance.  

DataInsights
Super User
Super User

@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' ) )
)

 

DataInsights_0-1703715429179.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.