cancel
Showing results for
Did you mean:  Helper II

## If columns contains "CASH", do this

Hi all, I am trying to do a calculation where if the category = "CASH", subtract paid out amount from MOP amount. Else, just show MOP amount.

Amount =
IF(
SELECTEDVALUE(Daily_MOP_Tb[MOP COMB]) = "CASH",
SUM(Daily_MOP_Tb[MOP_Amount]) - SUM(Paid_Out_Tb[Paid_Out_Amount]),
SUM(Daily_MOP_Tb[MOP_Amount])
)
I am not sure what to use to determine the category. Selectedvalue and values does not work. Thank you all!
1 ACCEPTED SOLUTION  Solution Specialist
IF(
MAX(Daily_MOP_Tb[MOP COMB]) = "CASH" && MIN(Daily_MOP_Tb[MOP COMB]) = "CASH",
SUM(Daily_MOP_Tb[MOP_Amount]) - SUM(Paid_Out_Tb[Paid_Out_Amount]),
SUM(Daily_MOP_Tb[MOP_Amount])
)
3 REPLIES 3  Solution Specialist
IF(
MAX(Daily_MOP_Tb[MOP COMB]) = "CASH" && MIN(Daily_MOP_Tb[MOP COMB]) = "CASH",
SUM(Daily_MOP_Tb[MOP_Amount]) - SUM(Paid_Out_Tb[Paid_Out_Amount]),
SUM(Daily_MOP_Tb[MOP_Amount])
)  Helper II

Have you tried creating a measure such as Test = selectedvalue(Daily_MOP_Tb[MOP COMB]) and dropping it in a table/matrix to see what it returns?
Also, it might be worth checking for spaces to make sure "CASH" isn't something like "CASH      " in your data...?  Community Champion

@abeirne is this a measure or a calculated column?
If column then write this:

``````IF(
Daily_MOP_Tb[MOP COMB] = "CASH",
Daily_MOP_Tb[MOP_Amount] - Paid_Out_Tb[Paid_Out_Amount],
Daily_MOP_Tb[MOP_Amount]
)`````` Showcase Report – Contoso By SpartaBI  