Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ChrisPW
New Member

count number of products that make money

I have two tables. First one is a list of products and the supplier.  Second is a list of sales including profit. I want to count the number of profitable products and then rank the manufacturers by that count.  Not sure where to start. Any help is appreciated

1 ACCEPTED SOLUTION

Hi,

 

I hope I understand your needs well,

from my understanding, you want to count every time sales line had profit and to connect it to the suppliers.

for example for product number 1 you had 6 sales 4 of them had profit so the supplier of product #1 will had 4 in the rank.

if you want you can change it to distinct count and then he will get only 1 for the rank from product #1

here is a screenshot, no need to write any code for it:

 

 1.PNG

View solution in original post

4 REPLIES 4
dilumd
Solution Supplier
Solution Supplier

@ChrisPW

 

Can you give some dummy data to work on?

Sure, See below (couldn;'t figure out how to attach a file)

 

Products:

Product KeyProduct NameSupplier
1Product 1Supplier 1
2Product 2Supplier 1
3Product 3Supplier 1
4Product 4Supplier 2
5Product 5Supplier 2
6Product 6Supplier 3
7Product 7Supplier 3
8Product 8Supplier 3
9Product 9Supplier 3

 

Sales:

OrderNoProduct KeyTotalSaleTotalProfit
1110010
211000
3110014
411005
511000
6110010
721000
8310076
9310043
10310023
1131002
12310010
1341001
144100312
1571009
16710023
1771008
18810023
1981000

Hi,

 

I hope I understand your needs well,

from my understanding, you want to count every time sales line had profit and to connect it to the suppliers.

for example for product number 1 you had 6 sales 4 of them had profit so the supplier of product #1 will had 4 in the rank.

if you want you can change it to distinct count and then he will get only 1 for the rank from product #1

here is a screenshot, no need to write any code for it:

 

 1.PNG

dilumd
Solution Supplier
Solution Supplier

@ChrisPW

 

This is a simple way of getting into ranking, but still if you need to get the ranking number you can use below formulae to get the ranking based on the profitable products.

 

CountProfitableOrders = CALCULATE(COUNTA(Sales[Total Profit]),FILTER(Sales,Sales[Total Profit] > 0))
RankNumber = RANKX(ALL(Products[Supplier]),Sales[CountProfitableOrders],,DESC)

1.JPG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.