Understand DAX with FILTER, ALL, REMOVEFILTERS,VALUES, ISINSCOPE, RANKX combinations

   Check list of all posts

The sample below is intended to show where to use FILTER, ALL (REMOVEFILTERS) and VALUES.  The model is very similar to the typical mode from SQL BI; you can even run it with https://dax.do/

There are three requirements:  1) Make total level show 100% for all years, incl. CY 2007, CY 2008 and CY 2009. 2) Make the percentage 100% against Catagory and subcategory, respectively.  The total percentage of all subcategories against one specific category is 100%. The total percentage of all products against a particular subcategory is 100%.  3) Only show top 3 products under each subcategory.

Step 0: Start from grand sales as below

MEASURE Sales[% Sales (1)] = 

VAR SalesAmount = [Sales Amount]
VAR AllSalesAmount =
    CALCULATE (
        [Sales Amount],
        -- Grand total
        REMOVEFILTERS ( Sales )
    )
VAR Result =
    DIVIDE (
        SalesAmount,
        AllSalesAmount
    )
RETURN

    Result

Step 1: Make total level show 100% for all years

MEASURE Sales[% Sales (2)] = 

VAR SalesAmount = [Sales Amount]
VAR AllSalesAmountYear =
    CALCULATE (
        [Sales Amount],
        -- Filter at Year level
        REMOVEFILTERS ( Sales ),
        VALUES ( 'Date'[Calendar Year] )
    )
VAR Result =
    DIVIDE (
        SalesAmount,
        AllSalesAmountYear
    )
RETURN
    Result

Step 2: Make the percentage 100% against Catagory and subcategory, respectively

MEASURE Sales[% Sales (3)] = 
VAR SalesAmount = [Sales Amount]
VAR AllSalesAmountYear =
    CALCULATE (
        [Sales Amount],
        --Filter at Year level
        REMOVEFILTERS ( Sales ),
        VALUES ( 'Date'[Calendar Year] )
    )
VAR AllSalesAmountYearCategory =
    CALCULATE (
        [Sales Amount],
        --Filter at Year level and Category level
        REMOVEFILTERS ( Sales ),
        VALUES ( 'Date'[Calendar Year] ),
        VALUES ( 'Product'[Category] )
    )
VAR AllSalesAmountYearSubCategory =
    CALCULATE (
        [Sales Amount],
        --Filter at Year level and Category level and Subcategory Level
        REMOVEFILTERS ( Sales ),
        VALUES ( 'Date'[Calendar Year] ),
        VALUES ( 'Product'[Category] ),
        VALUES ( 'Product'[SubCategory] )
    )
VAR Result =
    IF (
        ISINSCOPE ( 'Product'[Product Name] ),
        DIVIDE (
            SalesAmount,
            AllSalesAmountYearSubCategory
        ),
        IF (
            ISINSCOPE ( 'Product'[Subcategory] ),
            DIVIDE (
                SalesAmount,
                AllSalesAmountYearCategory
            ),
            DIVIDE (
                SalesAmount,
                AllSalesAmountYear
            )
        )
    )
RETURN
    Result

Step 2: Only show top 3 products under each subcategory.

MEASURE Sales[% Sales (4)] = 

VAR SalesAmount = [Sales Amount]
VAR AllSalesAmountYear =
    CALCULATE (
        [Sales Amount],
        REMOVEFILTERS ( Sales ),
        VALUES ( 'Date'[Calendar Year] )
    )
VAR AllSalesAmountYearCategory =
    CALCULATE (
        [Sales Amount],
        REMOVEFILTERS ( Sales ),
        VALUES ( 'Date'[Calendar Year] ),
        VALUES ( 'Product'[Category] )
    )
VAR AllSalesAmountYearSubCategory =
    CALCULATE (
        [Sales Amount],
        REMOVEFILTERS ( Sales ),
        VALUES ( 'Date'[Calendar Year] ),
        VALUES ( 'Product'[Category] ),
        VALUES ( 'Product'[SubCategory] )
    )
VAR RankXByTotal =
    IF (
        -- Remove all year without sales
        NOT ISBLANK ( [Sales Amount] ),
        RANKX (
            ALL ( 'Product'[Product Name] ),
            -- Rank based on total
            CALCULATE (
                [Sales Amount],
                ALL ( 'Date' )
            )
        )
    )
VAR Result =
    IF (
        ISINSCOPE ( 'Product'[Product Name] ),
        IF (
            RankXByTotal <= 3,
            DIVIDE (
                SalesAmount,
                AllSalesAmountYearSubCategory
            ),
            BLANK ()
        ),
        IF (
            ISINSCOPE ( 'Product'[Subcategory] ),
            DIVIDE (
                SalesAmount,
                AllSalesAmountYearCategory
            ),
            DIVIDE (
                SalesAmount,
                AllSalesAmountYear
            )
        )
    )
RETURN
    Result