Forum Discussion
ALLEXCEPT only working on visible row context
Hello,
I have a quick question. I am trying to calculate the $ Change (YOY) for a sub category, and then trying to compare that overall sub category change to specific products. Basically, we want to find products that are underperforming a sub category, but by using dynamic comparison. Here is the measure I am working on:
CALCULATE( [$ Vol Ch], ALLEXCEPT ( Data, Data[Date], Data[Market], Data[Sub-Cat] ) )
This works in a table or matrix if the "Sub-Cat" is being filtered or visible in the table (providing row context). However, I do not want to show the Sub-Cat in the table, because it can be assumed based on the product description (which is visible). So how can I change the measure to consider a 'non-visible row context'? Each product is already tied to a Sub-Category in the data (same data table, different column). Here is a screenshot of the data table with and without the sub-category visible:
Without sub category visible, numbers are incorrect
With sub category in table, numbers are correct
Thank you in advance and look forward to meeting everyone at the Data Insights Summit!
Bobby
Have you ever posted a question and then answered it immediately afterwards?
Here is the solution:
CALCULATE( [$ Vol Ch], ALLEXCEPT ( Data, Data[Date], Data[Market], Data[Sub-Cat] ), VALUES( Data[Sub-Cat] ) )
5 Replies
Have you ever posted a question and then answered it immediately afterwards?
Here is the solution:
CALCULATE( [$ Vol Ch], ALLEXCEPT ( Data, Data[Date], Data[Market], Data[Sub-Cat] ), VALUES( Data[Sub-Cat] ) )
Note, there is no row context in a table visual or a matrix visual. Row context only exists in a physical data model table (imported or calculated), and in a small subset of functions, the most common of which are FILTER(), *X() functions, and ADDCOLUMNS().
The reason that your original construction doesn't work is that there is no filter context on [Sub-Cat] in the matrix that doesn't include this field as a label.
VALUES() is evaluated in filter context, so your VALUES( 'Data'[Sub-Cat] ) is evaluated in the filter context of a given [Product Desc]. The result of this VALUES() is combined with a logical and with your ALLEXCEPT(). You should be able to remove 'Data'[Sub-Cat] from the ALLEXCEPT() and get the same result.
That makes a lot of sense. Thank you for your help!
I found this post after a 4 hour struggle, thank you.
