Forum Discussion
Help with wrong totals in matrix
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.
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.
4 Replies
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=b5wMMA8p3j2cR3pSThank you for your help.
I'll see, as your suggestion, what Alberto has to say.
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.
Hi,
Thank you for your DAX.
Works fine as expected.
Five stars !!!!
