Forum Discussion

hnguyen76's avatar
hnguyen76
Icon for Resolver II rankResolver II
6 years ago
Solved

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!

  • 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

10 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion

    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's avatar
      parry2k
      Icon for Super User rankSuper 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's avatar
        hnguyen76
        Icon for Resolver II rankResolver 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.