Ignoring a visual level Measure Filter, but keeping the slicer selection

[m4emily]

Hello community!

First time posting here, so please do not hesitate to ask if you need more details.

The Report context

The basic premise of the report is that the user can use a few slicers to select a portion of the company's products (Category, Sub-Category, Segment and Trend) and a slicer for the time period (usually current week -1 and the same period the previous year)

The user then gets 2 tables:

• The first one shows the total results for the selection made using the slicers by year
• The second table shows the top 10 selling items for each year. 
  • This particular table is also filtered through the slicer pannel by using a ranking DAX measure (with value lower or equal to 10)

The Problem

In my Top10 table, I want to add a contribution% column which would do the following calculation:

Sales of item / Sales of total selection 

For example (see capture of report): Sales of item A (137,578$) / Sales of the selection for 2023(1,851,206$)

No matter what way I turn the formula, I cannot seem to be able to avoid the visual level filter on the Ranking Measure, so my denominator tops out at 539,128 (total of top 10 for 2023) instead of getting the 1,851,206 (total of the selection for 2023)

Also important to note:

My model is in Live Connect - so I cannot create a column in the model

From what I've gathered, the ideal is to create a virtual table as a variable and then run a SUMX on it.

Something like this:

DENOMINATOR - Sales of selection =
VAR tabletopstyles =
    FILTER (
        KEEPFILTERS (
            SUMMARIZE (
                ALL ( Items ),
                Items[Style Image],
                "Sales", [Sales],
                "Rank", [_Top by Sales]
            )
        ),
        NOT ( ISBLANK ( [_Top by Sales] ) )
    )
RETURN
    SUMX ( tabletopstyles, [Sales] )

But after testing:

I cannot use REMOVEFILTERS as it will not take an expression as a second argument

Using ALL takes away all my slicer filters AND my visual level measure filter

Using ALLSELECTED keeps my slicer filters AND my visual level measure filter....HELP!

Last note: I've been able to do the opposite, which is bring my Top10 sales total back into my Total for all selection table at the top. But that table does not have a visual level filter. The formula was the following

Ctr% T10 - for sales =

VAR tabletopstyles =

    FILTER(

        KEEPFILTERS(

        SUMMARIZE(

        Items,

        Items[Style Image],

        "Sales",[Sales],

         "Rank",[_Top by Sales]

        )),

        AND(NOT(ISBLANK([Sales])), [_Top by Sales] <= 10))

RETURN

DIVIDE(SUMX(tabletopstyles,[Sales]), [Sales])

Ideas on alternatives are also welcome!

[amitchandak]

@m4emily , we will not be able to remove the measure filter , do better create measures with filter

like you can have TOPN measures

calculate([Net Sales PY], TOPN(2, allselected('Address'[Country ISO]), [Net Sales PY],desc), values('Address'[Country ISO]))

calculate([Net Sales], TOPN(2, allselected('Address'[Country ISO]), [Net Sales PY],desc), values('Address'[Country ISO]))

Both are TOP 2 on prior year sales

TOPN: https://youtu.be/QIVEFp-QiOk
TOPN with Numeric Parameter -https://youtu.be/cN8AO3_vmlY?t=26448