Help with wrong totals in matrix

[luisrossa8]

Hi,

In short my issue is I get wrong row and column total in a matrix if I use a measure to compute the value of the sales.

The data table is on the left.

On the right the first matrix has the totals correct.

The second matrix is not computing the totals right.

In this second matrix I used a measure like:

TotalSalesModified = if(SELECTEDVALUE(Sales[Item])="a" && SELECTEDVALUE(Sales[Month])="jan" ,99, sum(Sales[Sales]))

In the first one:

TotalSales = sum(Sales[Sales])

Can you help ?

Thanks in advance.

[Anonymous · Accepted solution]

Hello @luisrossa8 

For your question, I have created the following table.

Your original matrix looks like this:

You can use the following dax function to get the effect you want

TotalSalesModified = 
SUMX(
    Sales, 
    IF(
        Sales[Item] = "aa" && Sales[Month] = "jan", 
        99, 
        Sales[Sales]
    )
)

^{Best Regards,}

^Jayleny

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[luisrossa8]

Hi,

Thank you for your DAX.

Works fine as expected.

Five stars !!!!

[Anonymous]

@luisrossa8  You might need to use the SUMX and VALUES functions in your measure because of the aggregation measures do in the absense of those functions.

Alberto Ferrari explains it well here: https://youtu.be/6rgAkejrup8?si=b5wMMA8p3j2cR3pS

[luisrossa8]

Thank you for your help.

I'll see, as your suggestion, what Alberto has to say.