Sum Measure Grand Total in Visual is Blank

[hnguyen76]

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!

[az38 · Accepted solution]

Hi @hnguyen76 

whats result do you expect in Total of Bucket1?

If sum of each values of this column try

Bucket1 = sumx('Table';IF([Current] = [Previous]; [Current]; BLANK()))

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[parry2k]

@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]

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. 

[parry2k]

Don’t take me wrong, I will still add first or last date of the quarter and add date dimension in the model to avoid all this you are trying to achieve using these DAX expressions. Anyhow, good luck. You are in safe hands 😃

[az38]

@hnguyen76 

and so whatr are you expect to see in 

#Same Diff 

column? there are only two zeros in your example

do not hesitate to give a kudo to useful posts and mark solutions as solution

[hnguyen76]

Hi @az38,
I'm expecting to see -50,971.82 instead of the zeros

[az38]

@hnguyen76 

try

Bucket1 = calculate(sumx('Table';IF([Current] = [Previous]; [Current]; BLANK())))

do not hesitate to give a kudo to useful posts and mark solutions as solution

[hnguyen76]

@az38 ,

Same result. I'm only getting the zeros which are wrong in any case.

[az38]

@hnguyen76 

I cant repeat the error on my dummy data

maybe its context question or [current] or [previous] calculation

do not hesitate to give a kudo to useful posts and mark solutions as solution

[hnguyen76]

Hey @az38 ,
Turns out deleting and recreating my measure fixed it. Thanks for the help!