Measure to calculate percentage of slicer based on another slicer

[GarethW]

I am trying to create a pie chart that shows what percentage a selected slicer is of another selected slicer.

My data is formatted in the following way:

Company  |  Type       |  Booking

Comp A    | Strategic  | AB123

Comp A    | Strategic  | AB124

Comp B    | Strategic  | AB125

Comp C    | Tier 1       | AB332

Comp D   | Strategic  | AB333

I have a slicer that lets me pick the 'Type', which then updates the options in another slicer with the Companies of that type.

I want to pie chart to focus on the selected company, and work out it's share of all bookings in that type.

So if I select 'Comp A', the pie chart will show it as having 50% of the bookings for the 'Strategic' type (2 / 4).

If I then pick 'Comp C' it will become 100% of bookings for 'Tier 1' (1/1).

Each booking is a unique row in the data, with a 'Company' and 'Type' field.

I have tried to create a measure but nothing works the way I need it to.

Thanks in advance

[jdbuchanan71 · Accepted solution]

Hello @GarethW 

I can think of 2 ways to show it.  The measure is something like this:

Company % = 
VAR _Bookings = COUNTROWS(Bookings)
VAR _BookingsAll = CALCULATE( COUNTROWS ( Bookings ), ALLEXCEPT ( Bookings, Bookings[Type] ) )
RETURN DIVIDE( _Bookings, _BookingsAll )

Then you can use a guage to show the portion of 100 % for the selection or, if you want a pie chart you would need another measure to capture the remainder like so

Remainder = 1 - [Company %]

I have attached my sample file for you to take a look at.

If this solves your issues please mark it as the solution so that others can find it easily. Kudos 👍 are nice too.
John

[GarethW]

Thanks @jdbuchanan71 

Your code didn't work exactly for me, but I was able to make some amendments to get it to work, as both variables are based on a number of slicers/filters, with _Bookings just having an additional filter of the Company Name.

Share of Commerical Type = 
VAR _Bookings = CALCULATE(COUNTROWS('Raw Sailed Data'),ALLEXCEPT('Raw Sailed Data','Raw Sailed Data'[Comm Type Raw],'Raw Sailed Data'[Report Name Raw],'Raw Sailed Data'[Sail Month],'Raw Sailed Data'[Sail Year],'2019 Q1 hardcoded segments for ET'[SEGMENT_GROUPS]))
VAR _BookingsAll = CALCULATE(COUNTROWS('Raw Sailed Data'),ALLEXCEPT('Raw Sailed Data','Raw Sailed Data'[Comm Type Raw],'Raw Sailed Data'[Sail Month],'Raw Sailed Data'[Sail Year],'2019 Q1 hardcoded segments for ET'[SEGMENT_GROUPS]))
RETURN DIVIDE(_Bookings, _BookingsAll)