Forum Discussion

medaxing's avatar
medaxing
Frequent Visitor
5 years ago

DAX : Grand Totals Not Matching when zero or null

Hi Everyone,

 

I am bit struggling with the grand totals. Somehow the grand totals are not matching for few rows horizontally but works for others.

Below is the screenshot of the data and the dax code.

The vertical date is a posting date calendar and horizontal is the normal calendar.

The pattern where it does not work is whenever a row contains a Zero/Null value.

 

 

Dax code:

VAR prevmonth =
CALCULATE (
[M1] ,
PREVIOUSMONTH ( 'Calendar P'[P Date] )
)
RETURN
CALCULATE (
IF (
ISCROSSFILTERED ( 'Calendar P'[P D] ),
IF (
Max('C P'[P MonthKey]) <= (Max('C'[MonthKey])+100),
[M1] - prevmonth,
BLANK ()
),
CALCULATE (
[M1],
'T1'[Version] = 1
)
)
)

 

Any help is really appreciated.

Thanks in advance.

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    medaxing , force total from rows

     

    VAR prevmonth =
    CALCULATE (
    [M1] ,
    PREVIOUSMONTH ( 'Calendar P'[P Date] )
    )
    RETURN
    CALCULATE (sumx(values(Table[column]),  //Column you have used in column(pivot) of matrix
    IF (
    ISCROSSFILTERED ( 'Calendar P'[P D] ),
    IF (
    Max('C P'[P MonthKey]) <= (Max('C'[MonthKey])+100),
    [M1] - prevmonth,
    BLANK ()
    ),
    CALCULATE (
    [M1],
    'T1'[Version] = 1
    )
    )))

     

    Refer why grand total is wrong -https://www.youtube.com/watch?v=ufHOOLdi_jk

    • medaxing's avatar
      medaxing
      Frequent Visitor
      5 years ago

      Thanks Amit for the quick response.

      No luck though.

      I tried to separate the measure as well, still the same issue.