Measure is summing all columns in Matrix rather than reflecting the individual header

[SuddenClarity]

I realize this may be a very simple problem, but please bear with me as I am a DAX novice. 

I am trying to create two columns for each product, one that reflects current year sales and another that shows prior year sales. And each row represents a full year.

The data under the hood already aggregates everything by year. And in order to get the PY sales, I tried to create a measure with the following DAX code:

PY Sales =

VAR py_sales =

    CALCULATE(

       'Sales'[Sales],

       FILTER ( ALLSELECTED('Main Table'), [Year] = MAX ( 'Main Table'[Year]) -1 )

   )

RETURN

  py_sales

However, as you can see below. The PY Sales column is taking the sum of both Product 1 and Product 2 PY sales. 

Can anyone help me understand why my code is doing this? 

[v-jingzhang · Accepted solution]

Hi @SuddenClarity 

In your measure, ALLSELECTED('Main Table') removes all filters in the current context, including the Product filter. So it calculates the sum of all products. 

To keep the product filter, you can try this measure

PY Sales =
VAR py_sales =
    CALCULATE (
        'Sales'[Sales],
        FILTER (
            ALLSELECTED ( 'Main Table' ),
            [Year] = MAX ( 'Main Table'[Year] ) - 1
                && [Product] = MAX ( 'Main Table'[Product] )
        )
    )
RETURN
    py_sales

Or

PY Sales =
VAR curYear = MAX ( 'Main Table'[Year] )
RETURN
    CALCULATE (
        [Sales],
        ALLEXCEPT ( 'Main Table', 'Main Table'[Product] ),
        [Year] = curYear - 1
    )

Or

PY Sales =
VAR curYear = MAX ( 'Main Table'[Year] )
RETURN
    CALCULATE ( [Sales], ALLSELECTED ( 'Main Table'[Year] ), [Year] = curYear - 1 )

Reference:

ALLSELECTED 

ALLEXCEPT 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[v-jingzhang · Accepted solution]

Hi @SuddenClarity 

In your measure, ALLSELECTED('Main Table') removes all filters in the current context, including the Product filter. So it calculates the sum of all products. 

To keep the product filter, you can try this measure

PY Sales =
VAR py_sales =
    CALCULATE (
        'Sales'[Sales],
        FILTER (
            ALLSELECTED ( 'Main Table' ),
            [Year] = MAX ( 'Main Table'[Year] ) - 1
                && [Product] = MAX ( 'Main Table'[Product] )
        )
    )
RETURN
    py_sales

Or

PY Sales =
VAR curYear = MAX ( 'Main Table'[Year] )
RETURN
    CALCULATE (
        [Sales],
        ALLEXCEPT ( 'Main Table', 'Main Table'[Product] ),
        [Year] = curYear - 1
    )

Or

PY Sales =
VAR curYear = MAX ( 'Main Table'[Year] )
RETURN
    CALCULATE ( [Sales], ALLSELECTED ( 'Main Table'[Year] ), [Year] = curYear - 1 )

Reference:

ALLSELECTED 

ALLEXCEPT 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[SuddenClarity · Accepted solution]

Hi v-jingzhang, thanks for your solutions. 

Can you explain why ALLSELECTED removes all filters in the current context? I thought that it does the opposite? The documentation reads, "The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters." So I was under the impression that it would keep the context of the table including products? If not, how come your third solution works when we are not filtering for product in the formula?

Also I wanted to ask about the 1st solution. I have a seperate product table where the product names are coming from. Am I able to reference that table in the formula, or will I need to use the column in 'Main Table' that has a relationship with the product table? 

Thanks again for your assistance. 

[v-jingzhang]

Hi @SuddenClarity 

For the first solution, you can refer to the product table in the formula as the product names are from that table. Also it is suggested to refer to the product table. I used 'Main Table' because I didn't know whether there is such a product table in the model. 

For the first question, I'm not sure how to explain it better. I also struggled a lot when I started to use ALL/ALLSELECTED. Measures are affected by both explicit filters and implicit filters. Usually I take slicers/filters outside of the visual as explicit filters. ALLSELECTED will keep the filters and contexts from them. While in the matrix visual itself, it also has row and column filters that affect the values. I take them as implicit filters. In your matrix, Year is on the row and Product is on the Column. When you calculates the sales of previous year for the same product, you want to remove the year filter on the current row and use the previous year's value to filter it, but you also need to keep the product filter on the current column. That's why your measure calculates the sales of both products with ALLSELECTED('Main Table'). You pass the whole table to ALLSELECTED function which means it removes all implicit filters on the whole table. Instead, you only need to pass Year column to it because you only needs to remove the implicit filters on this specific column. That's why the third solution works. 

Hope I have explained it clearly. 😅

The following blogs explain the function with abundant examples:

https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/ 

https://blog.enterprisedna.co/the-difference-between-all-and-allselected-dax-functions-in-power-bi/ 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[SuddenClarity]

Thanks so much for the explanation, it really helped me understand the difference between the explicit vs. implicit filters.

Just a quick question on your first solution, do you know how I would be able to reference a second table in the FILTER function? I've tried several times, but to no avail. 

Thanks so much!