Forum Discussion
Measure is summing all columns in Matrix rather than reflecting the individual header
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:
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?
Hi Anonymous
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_salesOr
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:
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.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.
4 Replies
v-jingzhangCommunity Support
Hi Anonymous
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_salesOr
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:
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.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 Anonymous
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.