Context Filter: Problem with slicer

[Cling]

Hello!

I have a matrix with the category field in the rows and the total sales with the associate percentage in the columns. Additionally, I have a slicer with the categories to be able to filter. The desired behavior is that when you filter through the slicer, the percentage of sales is with respect to the total of that section, while if you do not filter anything in the slicer, it is with respect to the total. The problem is that the filter context is interfering with my implementation of this conditional.

An example is attached below:

Category Values: Cars, Bikes, Other

              

Thanks in advance

[Anonymous · Accepted solution]

Hi @Cling ,

Thanks for danextian's reply!
And @Cling , here is my sample data:

To achieve your desired result, you must have another table to create a slicer:

There is no relationship:

Then you can use this DAX to create a measure and put it into the table visual:

Sales Percentage = 
VAR _total = 
CALCULATE(
    SUM('Table'[Sales]),
    ALL('Table'),
    'Table'[Category] IN VALUES(Slicer[Category])
)
RETURN
SUM('Table'[Sales]) / _total

Then you need another DAX to create a measure as a filter for the table visual:

DaxFilter = 
IF(
    MAX('Table'[Category]) IN VALUES(Slicer[Category]),
    1,
    0
)

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian]

Hi @Cling 

Try this:

percentage =
DIVIDE (
    [sales],
    CALCULATE ( [sales], ALL ( 'table'[category] ) ) --apply to filter modifier to category so the value remains the same regardless of the filter coming from that column
)


or

percentage =
DIVIDE (
    SUM ( 'table'[amount] ),
    CALCULATE ( SUM ( 'table'[amount] ), ALL ( 'table'[category] ) ) --apply to filter modifier to category so the value remains the same regardless of the filter coming from that column
)