Forum Discussion
ALL Function - Mystery?
Hello ALL,
I'm encountering a problem/issue with this simple formula.
The problem must be with my data but I can't seem to figure out where?
So here's the Measure => County Total
County Total = CALCULATE ( [Total Net], ALL(Payments[Subcategory], Payments[Category]) ) Total Net = SUM ( Payments[Net] )
Basically I want the County Total to be listed in all rows of a Matix (so I can use as Denominator for a % calculation)
So I created some sample data to post here - Please note the formula works as intended with the sample data!
So the problem must be with my real data but I can't seem to figure out what could be causing this?
Any suggestions/ideas about what to look for would be greatly appreciated!
So with my real data the Measure seems to ignore the ALL ... Payments[Category] part of the formula
So there are 3 tables involved in this calculation Locations, Assets and Payments
Locations Table
| State | County | Location |
| ST1 | County 1 | Location 1 |
| ST1 | County 2 | Location 2 |
| ST1 | County 2 | Location 3 |
| ST1 | County 3 | Location 4 |
| ST1 | County 3 | Location 5 |
| ST1 | County 3 | Location 6 |
| ST1 | County 3 | Location 7 |
Assets Table
| Asset | Operator | Location |
| Asset 1 | Operator 1 | Location 1 |
| Asset 2 | Operator 2 | Location 2 |
| Asset 3 | Operator 2 | Location 2 |
| Asset 4 | Operator 2 | Location 2 |
| Asset 5 | Operator 2 | Location 3 |
| Asset 6 | Operator 3 | Location 4 |
| Asset 7 | Operator 3 | Location 5 |
| Asset 8 | Operator 3 | Location 6 |
| Asset 9 | Operator 4 | Location 7 |
Payments Table
| Company | Payment Date | Asset | Net | Category | Subcategory |
| Company 1 | 12/1/2016 | Asset 1 | $300.00 | Cat 2 | Sub 2.1 |
| Company 1 | 12/2/2016 | Asset 1 | $100.00 | Cat 3 | Sub 3.1 |
| Company 3 | 12/15/2016 | Asset 2 | $100.00 | Cat 1 | Sub 1.1 |
| Company 3 | 12/15/2016 | Asset 2 | $50.00 | Cat 1 | Sub 1.2 |
| Company 3 | 12/15/2016 | Asset 2 | $25.00 | Cat 1 | Sub 1.3 |
| Company 3 | 12/15/2016 | Asset 3 | $100.00 | Cat 1 | Sub 1.1 |
| Company 3 | 12/15/2016 | Asset 3 | $50.00 | Cat 1 | Sub 1.2 |
| Company 3 | 12/15/2016 | Asset 3 | $25.00 | Cat 1 | Sub 1.3 |
| Company 3 | 12/15/2016 | Asset 4 | $100.00 | Cat 1 | Sub 1.1 |
| Company 3 | 12/15/2016 | Asset 4 | $50.00 | Cat 1 | Sub 1.2 |
| Company 3 | 12/15/2016 | Asset 4 | $25.00 | Cat 1 | Sub 1.3 |
| Company 3 | 12/15/2016 | Asset 5 | $200.00 | Cat 1 | Sub 1.1 |
| Company 3 | 12/15/2016 | Asset 5 | $150.00 | Cat 1 | Sub 1.2 |
| Company 3 | 12/15/2016 | Asset 5 | $125.00 | Cat 1 | Sub 1.3 |
| Company 4 | 12/20/2016 | Asset 6 | $75.00 | Cat 1 | Sub 1.2 |
| Company 4 | 12/20/2016 | Asset 6 | $50.00 | Cat 1 | Sub 1.3 |
| Company 4 | 12/20/2016 | Asset 7 | $75.00 | Cat 1 | Sub 1.2 |
| Company 4 | 12/20/2016 | Asset 7 | $50.00 | Cat 1 | Sub 1.3 |
| Company 4 | 12/20/2016 | Asset 8 | $75.00 | Cat 1 | Sub 1.2 |
| Company 4 | 12/20/2016 | Asset 8 | $50.00 | Cat 1 | Sub 1.3 |
| Company 2 | 12/21/2016 | Asset 7 | $25.00 | Cat 2 | Sub 2.2 |
| Company 5 | 12/25/2016 | Asset 9 | $200.00 | Cat 2 | Sub 2.1 |
Again with the sample data the formula works fine - as intended!
I'm sure many of you have used something similar to calculate percentages.
Just hoping someone can suggest what to look for in my data that could be causing this???
Thanks!
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
15 Replies
Isn't the County Total measure suppose to be having sum function or it is just a typo
County Total = CALCULATE ( SUM([Total Net]), ALL(Payments[Subcategory], Payments[Category]) )
Also can you try which is very less likely the issue but would be good to try.
County Total = CALCULATE ( SUM([Total Net]), ALL(Payments))
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
