Calculating % of Total With Multiple Slicers

[cheid_4838]

I have two tables: 1.)invoice header that shows costs that are billed weekly on a given invoice, 2.) a stopdetail table that details all the deliveries that were billed on the invoice billed weekly. The only link between the two tables is the weekend date and a billto code. I also have four slicers (below) that I was asked to include.  Current state is when you switch between the different slicers, the values that appear for some weeks don't change because the values are not broken out at the detail level.  

I created a formula (below) that calcualted  % of total by line when I use the RouteID slicer.  This percentage was applied to the cost and correctly allocated the costs across each line when using the RouteID slicer. The problem I am having is that I need to do this same exercise with the StopID and StopDescription.

What is the best way to calculate % of total so that costs correctly allocated across each line so the correct weekly billedcharges are listed for each slicer that is used? 

I am probably making this harder than it needs to be, but nothing I have tried works.  Your help is appreciated.

Total Count = CALCULATE(sum('Stop Detail'[COUNT]),ALLSELECTED('Stop Detail'[RouteID]))

Use the results of this formula to apply against the cost and operational numbers.

Line Distribution Count =

DIVIDE(

    [Line Count],

    'Measures Table'[Total Count])

Stop Detail

Invoice

[VahidDM · Accepted solution]

Hi @cheid_4838 

Here are some relevant links that might help you:

1. DAX: How to calculate percentage (slicer choice) from fixed total
2. Percentage of filtered total with slicer
3. Calculation based on multiple slicers as total
4. Percent of Grand Total with Multiple Slicers
5. Calculate items percent based on slicers selection
6. Calculate Percentage value based on Slicer Selection
7. How to calculate the percentage of slicer selected total over grand total
8. How to get a percentage when using slicers
9. Create measure based on multiple slicer selections
10. Update a Percent Measure with Multiple Slicer Selections

Calculate the Total Count for each slicer:

For RouteID:

Total Count RouteID = CALCULATE(SUM('Stop Detail'[COUNT]), ALLSELECTED('Stop Detail'[RouteID]))
For StopID:

Total Count StopID = CALCULATE(SUM('Stop Detail'[COUNT]), ALLSELECTED('Stop Detail'[StopID]))
For StopDescription:

Total Count StopDescription = CALCULATE(SUM('Stop Detail'[COUNT]), ALLSELECTED('Stop Detail'[StopDescription]))
Calculate the Line Distribution Count for each slicer:

For RouteID:

Line Distribution Count RouteID = DIVIDE([Line Count], [Total Count RouteID])
For StopID:

Line Distribution Count StopID = DIVIDE([Line Count], [Total Count StopID])
For StopDescription:

Line Distribution Count StopDescription = DIVIDE([Line Count], [Total Count StopDescription])
Apply the percentage to the cost and operational numbers:

For RouteID:

Allocated Cost RouteID = [Line Distribution Count RouteID] * SUM('Invoice Header'[Cost])
For StopID:

Allocated Cost StopID = [Line Distribution Count StopID] * SUM('Invoice Header'[Cost])
For StopDescription:

Allocated Cost StopDescription = [Line Distribution Count StopDescription] * SUM('Invoice Header'[Cost])
Create a measure to dynamically switch between the slicers:

Allocated Cost =
SWITCH(
TRUE(),
ISFILTERED('Stop Detail'[RouteID]), [Allocated Cost RouteID],
ISFILTERED('Stop Detail'[StopID]), [Allocated Cost StopID],
ISFILTERED('Stop Detail'[StopDescription]), [Allocated Cost StopDescription],
SUM('Invoice Header'[Cost]) // Default case if no slicer is selected
)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[VahidDM · Accepted solution]

Hi @cheid_4838 

Here are some relevant links that might help you:

1. DAX: How to calculate percentage (slicer choice) from fixed total
2. Percentage of filtered total with slicer
3. Calculation based on multiple slicers as total
4. Percent of Grand Total with Multiple Slicers
5. Calculate items percent based on slicers selection
6. Calculate Percentage value based on Slicer Selection
7. How to calculate the percentage of slicer selected total over grand total
8. How to get a percentage when using slicers
9. Create measure based on multiple slicer selections
10. Update a Percent Measure with Multiple Slicer Selections

Calculate the Total Count for each slicer:

For RouteID:

Total Count RouteID = CALCULATE(SUM('Stop Detail'[COUNT]), ALLSELECTED('Stop Detail'[RouteID]))
For StopID:

Total Count StopID = CALCULATE(SUM('Stop Detail'[COUNT]), ALLSELECTED('Stop Detail'[StopID]))
For StopDescription:

Total Count StopDescription = CALCULATE(SUM('Stop Detail'[COUNT]), ALLSELECTED('Stop Detail'[StopDescription]))
Calculate the Line Distribution Count for each slicer:

For RouteID:

Line Distribution Count RouteID = DIVIDE([Line Count], [Total Count RouteID])
For StopID:

Line Distribution Count StopID = DIVIDE([Line Count], [Total Count StopID])
For StopDescription:

Line Distribution Count StopDescription = DIVIDE([Line Count], [Total Count StopDescription])
Apply the percentage to the cost and operational numbers:

For RouteID:

Allocated Cost RouteID = [Line Distribution Count RouteID] * SUM('Invoice Header'[Cost])
For StopID:

Allocated Cost StopID = [Line Distribution Count StopID] * SUM('Invoice Header'[Cost])
For StopDescription:

Allocated Cost StopDescription = [Line Distribution Count StopDescription] * SUM('Invoice Header'[Cost])
Create a measure to dynamically switch between the slicers:

Allocated Cost =
SWITCH(
TRUE(),
ISFILTERED('Stop Detail'[RouteID]), [Allocated Cost RouteID],
ISFILTERED('Stop Detail'[StopID]), [Allocated Cost StopID],
ISFILTERED('Stop Detail'[StopDescription]), [Allocated Cost StopDescription],
SUM('Invoice Header'[Cost]) // Default case if no slicer is selected
)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[cheid_4838 · Accepted solution]

For some reason the results are showing the same regardless of what is selected.  For example $115,148 was billed for the week of 10/5/2024.  When I select StopID ASLAW and then select ANCGOL which were both on the same billing I get the total billed amount. I would think I would get a much lower amount with all stopids or routids (whichever is selected) to add up to $115,148.  Should AllSelected be changed to ALL?

[cheid_4838]

I switched AllSelected to All and it seems to be working. Thanks for your help.