How to cancel filter context in virtual tables

[VeselaAlena]

I need to identify which products created 30% of total sales with virtual tables. I have succesfully identified the products in the virtual table but I'm not sure how to retrieve that information from the virtual table and put it into a visualization.

It seems that my results are getting filtered by filter context coming from the visualization no matter what I try.

in DaxStudio:

DEFINE
VAR_TotalSales =
    CALCULATE(
        [SalesWeb],
        REMOVEFILTERS('product'[product id])
    )

VAR _table =
    ADDCOLUMNS(
        VALUES('product'[product id]),
        "Product Sales", [SalesWeb]
    )

VAR _RunningTotal =
    ADDCOLUMNS(
        _table,
        "RunningTotal",
        VAR CurrentProduct = [Product Sales]
        RETURN
                SUMX(FILTER(_table, [Product Sales]>= CurrentProduct), [Product Sales])) 
    
VAR_percentage =
        ADDCOLUMNS( 
        RunningTotal, "Percent", 
             DIVIDE([RunningTotal], __TotalSales ), "Cows", IF(DIVIDE([RunningTotal], __TotalSales )<= 0.3, 1, 0))
    -- Transforming the running total into a percentage against the grand total of sales
    -- Cows are products generating top 30 % sales, we need to know which products are cows

EVALUATE
_percentage

This what I want to see in a visualization too (if the product is "cow" or not):

My goal is to have a matrix with product and column saying if the product is "cow" or not. But currently because of filter context influencing the measure that's build on virtual tables it always gives 1 for all the rows. I somehow need to cancel filter context coming in from the visualization. I've tried tried CALCULATETABLE but without success. 

[VeselaAlena · Accepted solution]

SOLVED:

The first issue was filter context which I solved with CALCULATETABLE + REMOVEFILTERS. And then I needed to FILTER the final result after RETURN.

cows =
VAR _totalHrubaMarze =
    CALCULATE (
        [Hrubá Marže Web Retail],
        REMOVEFILTERS ( 'product'[product name], 'product'[product id] )
    )
VAR _tabulka =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( 'product'[product id] ),
            "Hruba Marze Produktu", [Hrubá Marže Web Retail]
        ),
        REMOVEFILTERS ( 'product'[product name] )
    )
VAR _tabulka_RunningTotal =
    CALCULATETABLE (
        ADDCOLUMNS (
            _tabulka,
            "RunningTotal",
                VAR CurrentProduct = [Hruba Marze Produktu]
                RETURN
                    SUMX (
                        FILTER ( _tabulka, [Hruba Marze Produktu] >= CurrentProduct ),
                        [Hruba Marze Produktu]
                    )
        ),
        REMOVEFILTERS ( 'product'[product name] )
    )
VAR _percentage =
    ADDCOLUMNS (
        _tabulka_RunningTotal,
        "Procenta", DIVIDE ( [RunningTotal], _totalHrubaMarze ),
        "Kravy", IF ( DIVIDE ( [RunningTotal], _totalHrubaMarze ) <= 0.5, 1, 0 )
    )
RETURN
    SUMX (
        FILTER (
            _percentage,
            SELECTEDVALUE ( 'product'[product id] ) = 'product'[product id]
        ),
        [Kravy]
    )

[Greg_Deckler]

@VeselaAlena Use ALL, ALLEXCEPT, KEEPFILTERS or REMOVEFILTERS to modify filter context. 

[VeselaAlena]

Hi @Greg_Deckler, thanks for pointing me to the right direction.

I used CALCULATETABLE + REMOVEFILTER as shown below. Now the matrix shows number 88 on all rows (88 is correct if I want to know how many "cows" products we have but I need to know specifically which products are the cows). So I think two options:

1.) There's a faulty filtering logic already in the table VAR_table_RunningTotal. Because when I want to display the values from [RunningTotal] column in matrix visualization it also gives me same number in all rows. 

2.) DAX is correct and I'm just missing the last piece of how to translate the result of my virtual tables into matrix.

Cows = 
VAR _TotalSales =
CALCULATE (
    [SalesWebl],
    REMOVEFILTERS ( 'product'[product name] )
)
VAR_table =
CALCULATETABLE (
    ADDCOLUMNS (
        VALUES ( 'product'[product id] ),
        "Product Sales", [SalesWebl]
    ),
    REMOVEFILTERS ( 'product'[product name] )
)
VAR_table_RunningTotal =
CALCULATETABLE (
    ADDCOLUMNS (
        _table,
        "RunningTotal",
            VAR CurrentProduct = [Product Sales]
            RETURN
                SUMX (
                    FILTER (
                        _table,
                        [Product Sales] >= CurrentProduct
                    ),
                    [Product Sales]
                )
    ),
    REMOVEFILTERS ( 'product'[product name] )
)
VAR_percentage =
CALCULATETABLE (
    ADDCOLUMNS (
        _table_RunningTotal,
        "Percent",
            DIVIDE (
                -- Transforming the running total into a percentage against the grand total of sales.
                [RunningTotal],
                _TotalSales
            ),
        "Cows",
            IF (
                DIVIDE (
                    [RunningTotal],
                    _TotalSales
                ) <= 0.3,
                1,
                0
            )
    ),
    REMOVEFILTERS ( 'product'[product name] )
)

VAR _onlyCowsTable = FILTER (_percentage, [Kravy] = 1 )


RETURN
-- option 1.):
SUMX (_table_RunningTotal,[RunningTotal])

-- option 2.):
--SUMX (_percentage,[cows]) 

 Desktop Matrix: