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 throu...
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

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!