How to count 2 items in a single row with multiple items listed

[scyllanbay]

I have been spinnig my wheels trying to figure out the correct DAX formula to provide me with a total count of certain words in a single column.  Example:  I have a column for status with items such as "awarded", "not awarded", "pending award", "Lost", etc. etc.  I am looking for the correct way to tell the measure to count all of the awarded and not awarded in that same column.  Is that possible?  

[camargos88]

Hi @scyllanbay ,

Try this measure:

_Count = CALCULATE(COUNTA('Table'[Column1]), FILTER('Table', 'Table'[Column1] IN {"awarded", "not awarded"}))

If you need a distinct count, just use:

_Count = CALCULATE(DISTINCTCOUNT('Table'[Column1]), FILTER('Table', 'Table'[Column1] IN {"awarded", "not awarded"}))

[scyllanbay]

 I tried both of these and I am getting an error that says, "too many arguments were passed to the distinctcount function. the maximum argument count for the function is 1."  

[camargos88]

See this example:

[scyllanbay]

I got the formula to work and provide a 2... however, I need it to count all of the awarded and all of the not awarded and pull back that total number which may be 53.  Sorry, for the back and forth, I'm taking classes right now and learning but it's a tricky thing to learn. 

[scyllanbay]

I changed it to counta and that worked.  Is there now a way to add another column in this formula to pull out another item such as "hard bid".  So, in the end it would be giving me the total count of all awarded and not awarded in column A and the total hard bid in column B.  To get a true number of hard bids that were awarded and not awarded?

[camargos88]

@scyllanbay ,

You can use input the values here:

_Count = CALCULATE(COUNTA('Table'[Column1]), FILTER('Table', 'Table'[Column1] IN {"awarded", "not awarded", "OTHERS"}

and Column2 = "ABC"))

If you have a sample dataset I can help you with a pbix.

[scyllanbay]

Is there a way to attached a csv file on here?  

[camargos88]

@scyllanbay ,

You can use google drive / one drive for it, and share the link here.

[scyllanbay]

https://drive.google.com/file/d/1Z9SkhkZmm_635clOEL1uLFKKNo8890Sg/view?usp=sharing

I think that will work.  The first tab has my data and the second tab is what I am trying to create in Power BI.  Below is what I have so far, except the Total Hard Bid column is the one that I'm currently working on and it's 100% correct yet.   I have a lot of learning left to do and multiple classes on udemy already purchased 🙂

[camargos88]

@scyllanbay ,

I only see a csv file with Hard Bird and Negotiated, I believe there are 2 datasets here, right ?

Sorry I didn't get the connection with the first question. Can you explain it ?

[scyllanbay]

https://drive.google.com/file/d/1Zm3hzluvTFGdv50wv4yvB6bZwMNnK8V8/view?usp=sharing

The one that you are seeing the the graph that I'm trying to produce in Power BI to look like the below.  The attached is the actual data set. 

[camargos88 · Accepted solution]

@scyllanbay ,

See the file attached.

I didn't get how you calculated the Prediction column.

[scyllanbay]

Honestly, I'm speechless. You have no idea how much I appreciate this and didn't expect you to pull all of that together, but Thank you!

He calculated the prdiction column by multiplying the percentage by pending.  

One more question, there is an "R" showing before all of the $ values.  Do you know why that is?

[camargos88]

@scyllanbay ,

That's because I've formatted this column as currency, I'm in Brazil that's why you are seeing this "R$"....

You can change it clicking here:

[scyllanbay]

Thank you sooo much for helping me with this.  You have saved me so many hours of beating my head against my desk trying to figure this out.