cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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 III

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors