Filtering visually but not data

[charlotte_emily]

Hello,

I have a report which has a large table showing weekly %'s by tranport type.

I am wanting to add a slicer filter so that we can look at each type individually and not have to scroll through, but when filtering, the visual is changing the data. is there a way i can stop this? 

i have added the screenshot to show the highlighted column is one type, and correct data, and when i filter it, i am shown the second screenshot.

I am caluclating the % using the "show value as" and "percent of column total" of a measure counting the number of orders. 

[Anonymous]

Your solution is so great danextian , SamWiseOwl and rohit1991 

Hi, @charlotte_emily 

I wish you all the best. Previously Super user and community users have provided a solution to help you solve the problem. Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

Best Regards

Jianpeng Li

[danextian · Accepted solution]

Hi @charlotte_emily 

Create a separate dimension table for your index column or whatever the column name of those numbers are.

Relate that to your fact table with a one-to-many relationship.

Create this measure:

Percent to All Index =
DIVIDE (
    SUM ( Data[Value] ),
    -- Numerator: Calculates the total of the [Value] column in the current filter context.
    CALCULATE (
        SUM ( Data[Value] ),
        -- Denominator: Calculates the total of the [Value] column, 
        -- but overrides the current filter context as defined below.
        REMOVEFILTERS ( 'Index' ) -- Removes any filters applied to the 'Index' table or column(s). 
        -- This ensures that the denominator represents the grand total of [Value] across all 'Index' groups.
    )
)

Use that measure and the Index column from the dimension table in your visual

Please see the attached sample pbix

[rohit1991]

The issue you’re experiencing occurs because applying a slicer filter recalculates the percentages based on the filtered data, which is standard behavior for the "Show Value As" percentages in Power BI or similar tools. To maintain the original percentage calculation while filtering:

1. Create a Measure for Total Orders: Create a measure that calculates the total number of orders without considering the filter. For example:  TotalOrders = CALCULATE(COUNT(Table[Orders]), ALL(Table))

2. Create a Percentage Measure: Create a measure for the percentage calculation that references the unfiltered total: Percentage = COUNT(Table[Orders]) / [TotalOrders]
   
   

3. 1. Update Visuals: Use the new Percentage measure in your table or chart.

   This approach ensures the percentages remain based on the entire dataset, regardless of slicer filters applied. Let me know if you need help with implementation!

[SamWiseOwl]

Hi @charlotte_emily 

You can create a measure that ignores the external slicer.

% of all items =

Divide( --handles divide by 0 cases

           Sum(table1[column1]) --Your original unaggregated column

           ,Calculate(  Sum(table1[column1]), All(table1[column2])) --remove the filter on the slicer column
)

Calculate lets you change, add or remove filters.

The all function is telling PBI to remove the filter on the column which is being filtered.

Meaning the bottom calculation will return the unfiltered total.