Calculated Column or Other Option Picked A Single Amount Based on Several Criteria

To create a calculated column in Power BI that shows the amount with Transtype “N” only when the Project and Date are the same, you can use DAX. Here’s how you can do it:

Open Power BI Desktop and load your data.
Go to the Data view.
Create a new calculated column by clicking on the “New Column” button.
Then, use the following DAX formula:

Amount_N =
CALCULATE(
SUM(Table[Amount]),
Table[Transtype] = "N",
ALLEXCEPT(Table, Table[Project], Table[Date])
)

This formula will sum the Amount where Transtype is “N” and will consider only the rows where Project and Date are the same.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

Here’s how you can do it:

Create a new calculated column in Power BI.
Use the following DAX formula:
Amount_B =
IF(
CALCULATE(
COUNTROWS(Table),
Table[Transtype] = "N",
ALLEXCEPT(Table, Table[Project], Table[Date])
) = 0 &&
CALCULATE(
COUNTROWS(Table),
Table[Transtype] = "V",
ALLEXCEPT(Table, Table[Project], Table[Date])
) > 0 &&
CALCULATE(
COUNTROWS(Table),
Table[Transtype] = "B",
ALLEXCEPT(Table, Table[Project], Table[Date])
) > 0,
CALCULATE(
SUM(Table[Amount]),
Table[Transtype] = "B",
ALLEXCEPT(Table, Table[Project], Table[Date])
),
BLANK()
)

This formula does the following:

Checks if there are no rows with Transtype “N” for the same Project and Date.
Ensures there are rows with Transtype “V” and “B” for the same Project and Date.
If these conditions are met, it sums the Amount for Transtype “B”.
If the conditions are not met, it returns BLANK().
This should give you the value for “B” only when the conditions are satisfied.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!