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

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.