How to rank customers and show topN incidence over...

pedrocordovad · ‎09-21-2018

Hi everyone,

Im trying to rank customers of my company by revenue and show the % of the TopN over all the revenue. I would also like to show the same index but divided by each division of the company. My rows are work orders, so each customer might be in more than one row. My data is something like this:

Year	Work Order	Customer	Division	Revenue
2015	1	A	Div 1	50
2016	2	A	Div 2	60
2017	3	B	Div 3	70
2015	4	C	Div 2	80
2016	5	D	Div 3	90
2017	6	B	Div 1	100
2015	7	C	Div 3	110
2016	8	F	Div 1	120
2017	9	G	Div 1	130
2015	10	C	Div 3	140
2016	11	C	Div 3	150
2017	12	F	Div 3	160
2017	13	A	Div 2	170
2015	14	B	Div 2	180
2016	15	C	Div 2	190

So I have different work orders and I want to sum all the orders of each customer and rank them, but I don´t need to show the ranking, the goal is to show the % of top N overall. And I need this to be dynamic, as the page has a slicer of years.

I would really appreciate any help. Thanks!

v-danhe-msft · ‎09-23-2018

Hi @pedrocordovad,

From your description, could you mean sum the [Work Order] by [Customer] and calculate the percentage? If so, you could refer to below steps:

Create a new table:

New Table = SUMMARIZE('Table1','Table1'[Customer],"Sum of Work Order",SUM(Table1[Work Order]))

Create the relationship between the two tables:

Create a measure:

Top N% = SUM('New Table'[Sum of Work Order])/CALCULATE(SUM('New Table'[Sum of Work Order]),ALL('New Table'))

Result:

If I misunderstand you, could you please post your desired result if possible? Also, you could download my pbix file to have a view.

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

pedrocordovad · ‎09-24-2018

Thank you Daniel!

Eventhough your answer is not exactly what im looking for, I think its the best first step to get there. With that table I will be able to do what I want. But I have one problem creating the table. Everything is ok until I try to conect both tables ("table 1" with "new table"). The power bi says that "you cant create a relation between this two colums because one of them must have unique values". I don´t understand the problem since the new table by default should have unique values (each customer).

Maybe the problem is that customer colums in both tables must have unique values. In that case of course thats not possible, since the first table has many work orders for each customer. Could you help me with this? Again thank you very much!

Best regards,

Pedro