Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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!
@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!!!
Related to this post
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]) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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))
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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] ) )
@AnonymousThank You! That did it and I get why/how it works!
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?
@Anonymous
I'm just even more confused today...
I remembered this post here
1) So I loaded the tables in PowerPivot - and guess what my Measure produces the expected result with the real data!
2) Then I decided to try in a brand new pbix file - same thing - my Measure produces the expected result again with the real data!
I mean this is not a complicated measure! So it seems there's nothing wrong with my Data either.
I really really don't want to have to recreate this pbix file from scratch...
I've tried deleting all relationships and setting them up again - nothing seems to help fix the original pbix file
I've tried deleting all other tables in the data model except those 3 - no success
I've tried reversing the order of the columns in the Measure didn't make a difference BTW Thanks for the suggestion!
I've tried each column in its own ALL function - no change
And the weird thing is in PowerPivot and in a new pbix the Measure works as expected!
One final thought my Measure would not require the adjustments you mention regarding the Slicers!
Anyway if I figure it out I'll post back here!
But sadly it seems I'll have to recreate this file
It's good that your measure works as expected in a new PBIX file. It's BAD that you had to be inspired to create a new PBIX file for it to work - something's broken there, especially since ALLEXCEPT still strangely worked. I suggest you raise it as an issue with Microsoft.
The other issue you linked and the Microsoft response "The current Microsoft view is that this is by design behavior and customers should build their model with dimension tables if they need to use ALL functions in their calculations." surprises me:
At least we made some progress on it via this community, though not as simple as it could be.
Wow folks I figured it out!
@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!!!
Related to this post
I spent 2 days trying to figure this one out. Completely even forgot about setting that sort with a conditional calculated column. Cant belive something so silly would cause such trouble.
Thank you for the solution !
Good point. So not a bug, but a poorly documented feature/ side-effect/ use case... Now that we know with hindsight what to look for, it's simple! Also documented nicely here: https://blog.crossjoin.co.uk/2015/12/15/power-bi-desktop-sort-by-column-and-dax-calculations-that-us...
I wonder what we can do to avoid the next group also spending hours trying to figure out such an obscure issue?
Good point @Anonymous
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |