Creating a measure that uses values not selected in slicer

[Grevatious]

Hi Everyone,

I'm having a little bit of a problem trying to create a measure that uses values that are selected in a slicer with others that aren't selected. Let me explain it a little better.

So, I have this dataset:

Table: Transactions

Transaction id	Date	Status	Rejection Message
1	1/1/2021	Aproved
2	1/2/2021	Aproved
3	1/3/2021	Rejected	Communication Error
4	1/4/2021	Rejected	Fraud
5	1/5/2021	Rejected	Communication Error
6	1/6/2021	Reversed
7	1/7/2021	Aproved
8	1/8/2021	Reversed
9	1/9/2021	Rejected	Insuficient Funds
10	1/10/2021	Aproved

I have created a report that shows the detail information of all the rejected transactions. I also created 3 different slicers, one for the "Date", one for the transaction "Status" and the last one for the rejection "Message". So in this case I am trying to show all the rejected transactions caused by "Communication problems" whithin a certain date interval and also to calculate a rejection ratio which is something like: #Rejected transaction (by communication problems) / #Total Approved transactions.

And is this last part that I'm having problems with, trying to make the measure work because of the selected values in the slicers. I'm getting blanks and errors.

Now I have tried different approaches using ALL, EXCEPT, SELECTED VALUE but to no avail.

Could you please help me?

Thank you in advance!

[selimovd]

Hello @Grevatious ,

try the following measures:

#Rejected transaction (by communication problems) =
CALCULATE(
    COUNTROWS( myTable ),
    myTable[Rejection Message] = "Communication Error"
)

Then for the approved:

#Total Approved transactions = CALCULATE( COUNTROWS( myTable ), myTable[Status] = "Approved" )

And then at the end the ratio between these two:

Rejection Ratio =
DIVIDE(
    [#Rejected transaction (by communication problems)],
    [#Total Approved transactions]
)

Let me know if that worked.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[Grevatious]

Hey @selimovd thank you for your reply. Yes it works but because I have a "Status" slicer for the report, the "approved" measure doesn't work. In order for it to work It would have to somehow ignore those slicers.

[selimovd · Accepted solution]

Hey @Grevatious ,

you can remove the filter with the ALL function:

#Rejected transaction (by communication problems) =
CALCULATE(
    COUNTROWS( myTable ),
    myTable[Rejection Message] = "Communication Error",
    ALL( myTable[Status] )
)

Or for the approved transactions:

#Total Approved transactions =
CALCULATE(
    COUNTROWS( myTable ),
    myTable[Status] = "Approved",
    ALL( myTable[Status] )
)

The ratio should stay the same.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi