Forum Discussion
Help DAX Grand Total
I have a table with several columns that have a Grand Total. Only one column has a zero (0). The others are accurate.
I'm guessing it has something to do with the aggregation, but can't figure out what to do (plus, the same syntax is used for the other columns and they work). Hoping someone can help.
Here's my DAX:
PriorDay =
VAR NBDaily =
max(QMFact[datekey])
RETURN
CALCULATE(sum(QMFact[BoundCount]),FILTER ( QMFact,QMFact[datekey] = CALCULATE(NBDaily)))Any help will be much appreciated.
Hi jcampbell474 ,
As the DAX formula you shared, it seems the value of last day is what you want? If it match your case, you can try to modify your DAX to following.
PriorDay = VAR NBDaily = MAXX ( ALL ( QMFact ), QMFact[datekey] ) RETURN CALCULATE ( SUM ( QMFact[BoundCount] ), FILTER ( ALL ( QMFact ), QMFact[datekey] = CALCULATE ( NBDaily ) ) )If you just want to calculate the sum of one column, you can just create the following measure.
total = SUMX ( ALL ( QMFact ), QMFact[BoundCount] )
If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
BTW, pbix as attached.
Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more
2 Replies
Hi jcampbell474 ,
As the DAX formula you shared, it seems the value of last day is what you want? If it match your case, you can try to modify your DAX to following.
PriorDay = VAR NBDaily = MAXX ( ALL ( QMFact ), QMFact[datekey] ) RETURN CALCULATE ( SUM ( QMFact[BoundCount] ), FILTER ( ALL ( QMFact ), QMFact[datekey] = CALCULATE ( NBDaily ) ) )If you just want to calculate the sum of one column, you can just create the following measure.
total = SUMX ( ALL ( QMFact ), QMFact[BoundCount] )
If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
BTW, pbix as attached.
Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it moreThank you!
I had to remove ALL from the Filter as it displayed the total for everyone.
I should have used MAXX vs. MAX. MAX was getting the max date per row. MAXX gets the max of all dates. Which, I guess, is what was needed to get the elusive subtotal.Again, thank you!
