Subtracting based on a range of values - URGENT HELP !!!

[Anonymous]

Hello all,

     I have two matrix visualizations like this,

The values for both the matrix visuals are taken from a single table.

I want to subtract the values of both the sub-totals. But, keeping the 1st matrix's total as it is, say 190 and I want to select a range and sum the values, for example, for the account numbers 4 to 6, the summed up values will be 10+20+30 = 60. 

Then, I need to subtract the 190 (Sum of Matrix 1) with 60 (Sum of Matrix 2).

190 - 60 = 130.

Finally, that result 130 should be displayed in the 2nd Matrix itself or another matrix.

How can I achieve this?

I tried to achieve this with a slicer, it is not working as I expected. Could anyone please help to resolve this?

Sample Files are given below,

pbix_1

pbix_2

excel file

Thanks in Advance,

Praveen.

[parry2k · Accepted solution]

@Anonymous try this:

Total Difference = 
CALCULATE(
SUM(Table[Amount]), 
ALL( Table ) 
)
-
CALCULATE( 
SUM(Table[Amount]), 
Table[Account] IN {4,5,6} 
)

[parry2k]

@Anonymous so if i understood your requirement correctly , you want to subtract value of selected account (in a slicer) from total amount, correcT?

[parry2k]

@Anonymous is this range fixed? Always 4 to 6?

[parry2k · Accepted solution]

@Anonymous try this:

Total Difference = 
CALCULATE(
SUM(Table[Amount]), 
ALL( Table ) 
)
-
CALCULATE( 
SUM(Table[Amount]), 
Table[Account] IN {4,5,6} 
)

[Anonymous]

Thank you very much for your kindl response. But, if this range is high, say for instance if I have to select the values between the ranges of 40000 to 90000 out of 1 million accounts, how can I select, what could be the DAX for selecting this kind of ranges?

Regards,

Praveen.

[parry2k · Accepted solution]

Table[Account] >= 4 && Table[Account] <= 6

replace the IN condition with above logic to check the range. 

[Anonymous]

Awesome!!! Thank You very much.

Regards,

Praveen.

[Anonymous]

Yes this range is fixed.

[Anonymous]

Hi Parry,

     No. The slicer one is the example which i have tried myself. The actual requirement is, I need to subtract between both the matrices subtotal.

Sample PBIX file

1st matrix values are as it is coming from the source.

2nd matrix's value should be a range of selected values using DAX. say for ex,the sum of values of account numbers 4 to 6.

Sum of 1st matrix - sum of 2nd matrix (with the selected Range of values) = Result should be displayed on the second  matrix.

190 - 60 = 130

Thanks for your reply,

Praveen.