Forum Discussion
ALL Function - Mystery?
Sean,
Maybe it's related to some obscure cross-filtering issue from Payments to Asset to Location.
What do you get if you try:
County Total =
CALCULATE ( [Total Net], ALLEXCEPT ( Payments, Location[County] ) )Wow folks I figured it out! :smileyhappy:
AnonymousMystery solved!
The culprit was a Conditional Column created in the Query Editor
That Column was called Category Sort and I basically used it so in charts the Categories show in order of importance and not A-Z
If anyone wants to see what actually happens just follow these steps:
1) Load all 3 sample tables I posted on Page 1
2) Write these 2 Measures
Total Net = SUM ( Payments[Net] ) County Total = CALCULATE ( [Total Net], ALL ( Payments[Subcategory], Payments[Category] ) )
3) Create a Matrix with County, Category and Subcategory in the Rows and then add the 2 Measures to the Values
Everything works great!
4) Now click Edit Queries and Add a Conditional Column in the Payments Table =>Category Sort
you can use the UI and basically
if Category is Cat 2 then 1, else if Category is Cat 3 then 2, otherwise 1 => OK => Close and Apply
so far so good nothing is affected yet!
5) Now go to the Data View => Payments table
=> select the Category Column => click Sort By Column => select the Category Sort column
6) Now go back and look at the Matrix => the County Total Measure no longer works as intended
HAPPY NEW YEAR!!! :smileyhappy:
Related to this post
Hey thanks for replying.
1) Total Net is a already a Measure = SUM ( Payments[Net] ) I had listed it under the County Total Measure above
2) ALL(Payments) gives the Overall Total Net for all Counties
You are right, you need total by county regardless what category/sub category it is. Not sure why this will work on sample data and not on real data.
I was referring to your "County Total" which missing SUM function, I'm sure that was typo.
Sean,
Maybe it's related to some obscure cross-filtering issue from Payments to Asset to Location.
What do you get if you try:
County Total =
CALCULATE ( [Total Net], ALLEXCEPT ( Payments, Location[County] ) )Good point Anonymous
AnonymousThank You! That did it and I get why/how it works! :smileyhappy:
But I'm still wondering however why my original Measure works with the sample data I created but not with my real data
The relationships are set up the same way as in the sample - go figure...
Anyway Thanks again!
That's good news, though the new version of the measure will likely cause problems if you want to also filter on Date, Company etc. - you'll need to add any slicer columns into the ALLEXCEPT to have the measure respect them I think, if that is required.
I can't see what's wrong with your original measure either, especially as you have single direction cross filters in place - there shouldn't be any weird filtering up to Asset and back down to Payment.
This shouldn't be hard!
I wonder if you get a different result in your original measure if you change the order of the columns in the ALL:
County Total = CALCULATE ( [Total Net], ALL(Payments[Category], Payments[Subcategory]) )
Or do you have any unexpected cross-pollination of Category and Subcategory - e.g. Sub 2.1 in Cat3 and Cat2?