Forum Discussion

charlotte_emily's avatar
charlotte_emily
New Member
1 year ago
Solved

Filtering visually but not data

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. 

 

 

 

 

 

Need Help
  • danextian's avatar
    danextian
    1 year ago

    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

4 Replies

  • SamWiseOwl's avatar
    SamWiseOwl
    Super User
    1 year ago

    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.

     

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    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]

      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!

  • danextian's avatar
    danextian
    Super User
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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