Forum Discussion
Except Function
I have a table going to show example below but i need to use a countif almost statement excluding certian data. Not sure how to do it.
So example below i have customers and i can calculate for team 1 and 2 the totals but there is answers outside of this i need to calculate in an other column. So i need to take my total responses and somehow count those but subtract any answers which are team 1 and team 2. The data calculating team 1 and team 2 are in different data sets in my real life data scenario as well if that matters. How do i countif with exclusion. Hope this makes sense, thanks!
Customer Total Responses Team 1 Team 2 Other
Stan 10 2 5
Ann 15 6 4
Bill 12 8 1
Hi savannahr ,
Since you didn't provide your data model, I could only calculate all the fields in one table.
Below is my test table:
Please try to create a new column with below dax formula:
Other = [Total Responses]-[Team 1]-[Team 2]Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi savannahr ,
Since you didn't provide your data model, I could only calculate all the fields in one table.
Below is my test table:
Please try to create a new column with below dax formula:
Other = [Total Responses]-[Team 1]-[Team 2]Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.savannahr Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Expected would be the net difference of total responses minus the team 1 and team 2 ones listed in other something like below.
Customer Total Responses Team 1 Team 2 Other
Stan 10 2 5 3
Ann 15 6 4 5
Bill 12 8 1 3
Hope this helps.
Greg_Deckler did not at mention you in response apoligies.
