Forum Discussion
Sum Measure Grand Total in Visual is Blank
Good morning, All.
I have a few simple measures that seems to give me the correct numbers but my grand total is showing as blank. I can't for the life of me understand why my total isn't showing. To explain, I am summing up a field called Difference and doing a comparison just to see if the previous quarter's numbers matches the current quarter's numbers.
My base measure is a typical sum:
Total Difference = SUM('Table'[Difference])
To calculate my previous quarter's numbers, I had to do some dax magic:
Previous =
var _SelectedPeriod = SELECTEDVALUE('Table'[Year_Quarter], MAX('Table'[Year_Quarter]))
var _QuarterNum = VALUE(RIGHT(PATHITEM(SUBSTITUTE(_SelectedPeriod, "-", "|"), 1, TEXT), 1))
var _YearNum = PATHITEM(SUBSTITUTE(_SelectedPeriod, "-", "|"), 2, INTEGER)
var _PreviousPeriod = IF(_QuarterNum = 1, "Q4-" & _YearNum-1, "Q" & _QuarterNum - 1 & "-" & _YearNum)
RETURN
CALCULATE([Total Difference], 'Table'[Year_Quarter] = _PreviousPeriod)
Then I calculated my current quarter's numbers which is a bit simpler:
Current =
var _SelectedPeriod = MAX('Table'[Year_Quarter])
RETURN
CALCULATE([Total Difference], 'Table'[Year_Quarter] = _SelectedPeriod)
Finally, this is where it gets confusion. My logic is if current difference = previous difference, return me current difference. The numbers comes out correct it's just that the grand total isn't there:
Bucket1 =
var _Current = [Current]
var _Previous = [Previous]
RETURN
IF(_Current = _Previous, _Current, BLANK())
EDIT: Here's a permalink to a sample PBI: https://1drv.ms/u/s!AjMAdFmWoN-JakiK6JBX64vNwSw?e=LJFZyp
Any help would be greatly appreciated!
10 Replies
- az38
Community Champion
- parry2k
Super User
hnguyen76 I would highly recommend to use time intelligence functions by adding calendar in your model. There is no need to write complex DAX code when this can be achieved by using in-built functions and can provide scalable solution. Let's not solve the problem for the purpose of solve it but think how to approach with scalable and simpler way.
Here is more details on time intelligence function and you can find many posts on how to add calendar in your model.
Would appreciate Kudos 🙂 if my solution helped.
- hnguyen76
Resolver II
Hi az38 ,
Thanks for the response. -29,816.42 was the expected total and the SUMX did work and in terms of the sample data but when I applied it to my actual real numbers (which uses the same table) it gives me a different result:The formula is as followed:
#Same Diff = SUMX(Dim_L1_Historical, IF([Current] = [Previous], [Current], BLANK()))parry2k ,
Thanks for your response and I normally would use a date table to create time intelligence calculations but I'm working with a data set that does not have an actual date. For this current iteration, it would be more work to convert the string into a date.