Errors of "row subtotals" of a matrix

[johnyip]

I have a hypothetical set of data as shown as beow figure: sales of 5 shops and their respective sales and target. The "Sales vs Target" is a measure simply defined by subtracting target from sales.

Note that there is a shop (shop E) that does not have any target. I have deliberately specified that in the measure to refrain "Sales vs Target" from displaying the figure (1245) which is wrong.

However, the total fails to comply with the deliberated result, and have that 1245 included in the calculation (200-500+2300+200=2200 only. The calculated 3445 counts the 1245 that I deliberately missed out.)

Do I do nything wrong in defining the measure? Please help.

https://drive.google.com/file/d/1TT3NHYn4ILF_TySILLLcBx9pfmN1Q6gj/view?usp=sharing

[davehus · Accepted solution]

Hi @johnyip ,

Please try 

Measure = CALCULATE(SUMX(Sheet1,Sheet1[Sales]-Sheet1[Target]), FILTER(Sheet1,Sheet1[Target]>0))

Did I help you today? Please accept my solution and hit the Kudos button.

[v-yalanwu-msft · Accepted solution]

Hi, @johnyip ;

May be you could modify the measure .

Sales vs Target = 
CALCULATE( SUM(Sheet1[Sales])-SUM([Target]),FILTER('Sheet1',[Target]<>BLANK()))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnyip]

Thanks all for the reply and attempts to help. I have taken my temporary solution as the final solution of my issue. Thx.

[v-yalanwu-msft · Accepted solution]

Hi, @johnyip ;

If it the measure. you could try it.

Measure = CALCULATE([Sales2]-[Target2],FILTER('Sheet1',[Target2]<>BLANK()))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnyip]

Hello all, thanks for the replies and they all work.
But how about if [Sales] and [Target] are both measures?

I have been enlightened by your answers and get a temp workaround to achieve the correct result: creating a column using DAX to record which shop is excluded and use that column as the filter.

Just want to know if there is any proper way (measure DAX) to do that if [Sales] and [Target] are both measures?

[v-yalanwu-msft · Accepted solution]

Hi, @johnyip ;

May be you could modify the measure .

Sales vs Target = 
CALCULATE( SUM(Sheet1[Sales])-SUM([Target]),FILTER('Sheet1',[Target]<>BLANK()))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[davehus · Accepted solution]

Hi @johnyip ,

Please try 

Measure = CALCULATE(SUMX(Sheet1,Sheet1[Sales]-Sheet1[Target]), FILTER(Sheet1,Sheet1[Target]>0))

Did I help you today? Please accept my solution and hit the Kudos button.