Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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:
Sure, See below (couldn;'t figure out how to attach a file)
Products:
Product Key | Product Name | Supplier |
1 | Product 1 | Supplier 1 |
2 | Product 2 | Supplier 1 |
3 | Product 3 | Supplier 1 |
4 | Product 4 | Supplier 2 |
5 | Product 5 | Supplier 2 |
6 | Product 6 | Supplier 3 |
7 | Product 7 | Supplier 3 |
8 | Product 8 | Supplier 3 |
9 | Product 9 | Supplier 3 |
Sales:
OrderNo | Product Key | TotalSale | TotalProfit |
1 | 1 | 100 | 10 |
2 | 1 | 100 | 0 |
3 | 1 | 100 | 14 |
4 | 1 | 100 | 5 |
5 | 1 | 100 | 0 |
6 | 1 | 100 | 10 |
7 | 2 | 100 | 0 |
8 | 3 | 100 | 76 |
9 | 3 | 100 | 43 |
10 | 3 | 100 | 23 |
11 | 3 | 100 | 2 |
12 | 3 | 100 | 10 |
13 | 4 | 100 | 1 |
14 | 4 | 100 | 312 |
15 | 7 | 100 | 9 |
16 | 7 | 100 | 23 |
17 | 7 | 100 | 8 |
18 | 8 | 100 | 23 |
19 | 8 | 100 | 0 |
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:
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)
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |