Forum Discussion
Countrows from Distinctcount from another column
Hi All,
I'm trying to do a count of all values from one column... got that, not a problem. The problem is that I need my new count based on a distinct count from another column. For example column A is sales orders, and column B is B2B or B2C values......some sales orders have multiple lines, I need to count how many rows are B2B and how many rows are B2C, but need the disntinct count of the sales orders (can't have the B2B count multiple rows.
I'm using B2B Count = IF(FIND("B2B",SalesOrder[B2B/B2C],1,-1) = -1,0,1) to count all my B2B, but it's counting *ALL* rows, and many sales orders have multiple lines. I'm not sure how to make the distinct count from the sales order column query with my B2B Count.
Thanks for any help!
Good to know that you can solve your question.
Please accept the solution to others con use you.
8 Replies
Hi, a few ways to obtain:
Alternative 1:
Alternative 2. Using a Measure:
Measure = CALCULATE ( DISTINCTCOUNT ( Table1[SalesOrder] ) )
Add a Visual and use B2B/B2C Column and the measure
Regards
Victor
But I'm going to need the counts of distinct B2B and B2C to do invoicing, and I cannot pull that from a Visual can I?
Victor, thank you very much! That works, but now I am stuck to figure out how to make a cost calculation. B2B = $2.50 ea, and B2C = $1.65 ea. I'm not sure how to get that in there. I have a calculated column with the value (price) for each, but not sure how to make it multiply by only B2B and distinct # of orders, and B2C??
