Countrows from Distinctcount from another column

[Debbie_Montique]

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!

[Vvelarde]

@Debbie_Montique

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

[Debbie_Montique]

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??

[Vvelarde]

@Debbie_Montique

Can you post a sample of the data to replicate it.

Regards

VIctor

[Debbie_Montique]

Hello,

Below is the data, please note that for each distinct SO Document that is B2B - price is $2.50, and for each distinct SO Document that is B2C - price is $1.65.

S.O.Document	B2B/B2C
36094812	B2B
36150140	B2B
36211362	B2B
36037098	B2C
36094813	B2C
36113575	B2C
36132013	B2C
36150156	B2C
36189237	B2C
36049426	B2B
36066981	B2B
36096529	B2B
36159728	B2B
36159733	B2B
36170533	B2B
36211343	B2B
36244071	B2C
36245048	B2B
36034609	B2B
36043892	B2B
36045997	B2B
36046243	B2C
36046444	B2B
36071046	B2B
36170505	B2B
35979970	B2B
36008473	B2B
36032993	B2B
36035768	B2B
36036393	B2B
36044494	B2B
36045814	B2B
36046268	B2B
36049434	B2B
36049435	B2B
36056082	B2B
36056085	B2B
36058503	B2B
36058504	B2B
36058513	B2B
36058515	B2B
36058516	B2B
36058517	B2B
36058518	B2B
36058519	B2B

Here in my table I have the # of distinct orders by B2B/B2C as you suggested above, but I cannot multiply out the price per # of distinct orders:

Thank you very much!

[Vvelarde]

@Debbie_Montique

Try with this measure:

Price =
IF (
    SELECTEDVALUE ( Table1[B2B/B2C] ) = "B2B";
    DISTINCTCOUNT ( Table1[S.O.Document] ) * 2,5;
    DISTINCTCOUNT ( Table1[S.O.Document] ) * 1,65
)

You can made dinamically replacing the HardCoded Price with the AVG of the calculated column.

Regards

Victor 

[Debbie_Montique]

Victor, you are my HERO!!!  That works PERFECTLY.  I cannot thank you enough.  THANK YOU THANK YOU!!!

[Vvelarde · Accepted solution]

@Debbie_Montique

Good to know that you can solve your question.

Please accept the solution to others con use you.

[Debbie_Montique]

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?