Forum Discussion

hilary_tesillo's avatar
hilary_tesillo
Advocate I
4 years ago
Solved

Total column matrix Incorrect

Hi.

I have the following matrix with the measures Sales and Goal by month; I have a third measure called Sales*, this shows the value of Sales if the month is not closed yet or shows the value of Goal otherwise; the dax expression for this measure is shown in the second image. The problem is that the Total show in the matrix is not correct given the values in every column.

Thank you in advance for your help.

 

 

 

DAX
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi hilary_tesillo 

    please try

    Sales* =
SUMX (
    VALUES ( 'Calendario Venta'[Month Name] ),
    CALCULATE (
        VAR reportingDate =
            MAX ( 'BC Ventas'[Fecha Exp] )
        VAR endMonth =
            EOMONTH ( reportingDate, 0 )
        RETURN
            IF (
                OR ( reportingDate < endMonth, ISBLANK ( reportingDate ) ),
                SUMX ( VALUES ( 'Calendario Venta'[Date (bins)] ), Medidas[Goal] ),
                SUMX ( VALUES ( 'Calendario Venta'[Date (bins)] ), [Sales] )
            )
    )
)

1 Reply

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi hilary_tesillo 

    please try

    Sales* =
SUMX (
    VALUES ( 'Calendario Venta'[Month Name] ),
    CALCULATE (
        VAR reportingDate =
            MAX ( 'BC Ventas'[Fecha Exp] )
        VAR endMonth =
            EOMONTH ( reportingDate, 0 )
        RETURN
            IF (
                OR ( reportingDate < endMonth, ISBLANK ( reportingDate ) ),
                SUMX ( VALUES ( 'Calendario Venta'[Date (bins)] ), Medidas[Goal] ),
                SUMX ( VALUES ( 'Calendario Venta'[Date (bins)] ), [Sales] )
            )
    )
)