Forum Discussion
Calculated Column or Other Option Picked A Single Amount Based on Several Criteria
Hi,
Based from the screenshot. I want to show the amount with Transtype N only when project and date are the same.
Thanks...tksnota...
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!
8 Replies
- HCA
Advocate I
Hi, You will need a DAX measure that checks the condition for Transtype = N and compares the current project and date to the corresponding Transtype = N row.
Please try this measure:
Filtered Amount =
VAR CurrentDate = SELECTEDVALUE('YourTable'[Date])
VAR CurrentProject = SELECTEDVALUE('YourTable'[project])
RETURN
IF(
AND(
SELECTEDVALUE('YourTable'[Transtype]) = "N", // Check if the row is for Transtype N
CALCULATE(
COUNTROWS('YourTable'),
'YourTable'[Transtype] = "N",
'YourTable'[Date] = CurrentDate,
'YourTable'[project] = CurrentProject
) > 0 // Ensure that there is a matching row with Transtype N for the same date and project
),
SELECTEDVALUE('YourTable'[Amount]),
BLANK() // If the condition is not met, return blank
)
If this helps, please let me know.
Singamshetty994 and HCA
I tried both of your dax and add them to the visual but it resulted on my table being blanks as shown
- saud968
Memorable Member
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!- saud968
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!
Hi Anonymous
Create a new measure in your Power BI model with the below DAX code you can achieve the Amount for unique combinations of Date and Project where Transtype is 'N'.
FilteredAmount =
CALCULATE(
SUM(YourTableName[Amount]),
FILTER(
ALLEXCEPT(YourTableName, YourTableName[Date], YourTableName[Project]),
YourTableName[Transtype] = "N"
)
)
If you find this information helpful please accept this as solution. Thank you in advance.Hi Anonymous
Create a new measure in your Power BI model with the below DAX code you can achieve the Amount for unique combinations of Date and Project where Transtype is 'N'.
FilteredAmount =
CALCULATE(
SUM(YourTableName[Amount]),
FILTER(
ALLEXCEPT(YourTableName, YourTableName[Date], YourTableName[Project]),
YourTableName[Transtype] = "N"
)
)
If you find this information helpful please accept this as solution. Thank you in advance.
