Forum Discussion

David83300's avatar
David83300
Frequent Visitor
2 years ago
Solved

Fix Matrix Total in Dax Measure

Dear Power BI Community,

 

I would like some help to fix the calculation of a total in a matrix visualisation.

 

As you can see in the table below, the total for ยซ Starting Amount ยป in 2023 is not working :

 

Here is my measure :

 

StartingAmount=
    CALCULATE(
    Sum(Amount),
       PREVIOUSYEAR(DIM_DATE[Date]),
       DIM_DATE[MoisNom] = "Dec"
)

 

 

I found a solution using the SUMMARIZECOLUMNS function, but this way is not very practical (I would like to use a measure without creating new table in my model). Do you have an idea ?

 

 

Test_SUMMARIZECOLUMNS =
SUMMARIZECOLUMNS(
    DIM_DATE[Year],
    DIM_DATE[Month],
    "Amount",[Amount],
    "StartingAmount",
    CALCULATE(
        [Amount],
        PREVIOUSYEAR(DIM_DATE[Date]),
        DIM_DATE[MoisNom] = "Dec"
    )
)

 

 

 

 

Kind regards,

David

  • While waiting to find a proper solution, I found another way using the ISINSCOPE function:

     

    Something like that:
    if(ISINSCOPE(DIM_DATE[Month]),

    sum(Amount),
    sum(Amount)*max(DIM_DATE[NumMonth]))

     

    Dirty but it works ๐Ÿ˜‰

1 Reply

  • David83300's avatar
    David83300
    Frequent Visitor

    While waiting to find a proper solution, I found another way using the ISINSCOPE function:

     

    Something like that:
    if(ISINSCOPE(DIM_DATE[Month]),

    sum(Amount),
    sum(Amount)*max(DIM_DATE[NumMonth]))

     

    Dirty but it works ๐Ÿ˜‰