cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Resolver III
Resolver III

Re: count number of products that make money

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
Highlighted
Solution Supplier
Solution Supplier

Re: count number of products that make money

@ChrisPW

 

Can you give some dummy data to work on?

Highlighted
New Member

Re: count number of products that make money

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
Highlighted
Resolver III
Resolver III

Re: count number of products that make money

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

Highlighted
Solution Supplier
Solution Supplier

Re: count number of products that make money

@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
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors