Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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!
Solved! Go to Solution.
Good to know that you can solve your question.
Please accept the solution to others con use you.
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
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??
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!
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
Victor, you are my HERO!!! That works PERFECTLY. I cannot thank you enough. THANK YOU THANK YOU!!!
Good to know that you can solve your question.
Please accept the solution to others con use you.
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?
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
65 | |
63 | |
52 | |
37 | |
36 |
User | Count |
---|---|
79 | |
67 | |
60 | |
45 | |
45 |