Solved: Ranking based line chart

kompalli · ‎11-17-2022

Hi all,

Actually I am trying to create a line chart by using ranking.

Sample scenerio:

If I have company A with many products and other companies(like B,C, D considered as others).I have ranked based on sales for 'A' and others individually and took Top 1 rank of 'A' company and top 1 rank of others same way based on that I have considered top 5 ranks of 'A' and top 5 ranks of others individually. Now I want to combine all these to get a line chart that displays top 5 products of company 'A' based on ranks considered and top 5 products of others as a line chart. Any suggestion would be truly be appreciated and helpful.

Thanks in advance.

v-rongtiep-msft · ‎11-21-2022

Hi @kompalli ,

Please refer to my pbix file to see if it helps you.

Create a column.

Column = var _1= RANKX(FILTER(ALL('Table'),'Table'[A and others]=EARLIER('Table'[A and others])),'Table'[Sales],,DESC,Dense)
return
IF(_1<=5,_1,BLANK())

Then filter the line chart: Column is not blank.

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output with more details.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

kompalli · ‎11-23-2022

Hi @v-rongtiep-msft ,

Thanks for your response. Below are the measures for your reference that I have used to rank company "A" and "others" based on product, company and sales measures. It was working fine but when used as measure, the same logic I couldn't apply it to a calculated column.

Rank by A Sales = if (SELECTEDVALUE (Table name [Company]) ="A”, RANKX (

SUMMARIZE (ALL (Table name), Table name [Product], Table name [Company]), [Sales measure], CALCULATE ([Sales measure], Table name [company] = "A"), DESC, Dense))

Rank by others Sales = if (SELECTEDVALUE (Table name [Company]) ="others”, RANKX (SUMMARIZE (ALL (Table name), Table name [Product], Table name [company), [Sales measure], CALCULATE ([Sales measure], Table name [company] = "others"), DESC, Dense))

These are the measures used to rank based on company “A” and “others” but based on these conditions a calculated column is required. I tried doing it but I couldn't get the desired ranking.

Below image is that shows the expected output in detail.
There might be similar products repeating for a company over a period based on sales.

Below is the sample data for your reference.

Company	Product	Date	Sales	A and others
A	A01	01-Feb-20	50	A
A	A02	01-Feb-20	36	A
A	A03	01-Feb-20	43	A
A	A04	01-Feb-20	31	A
B	B01	01-Feb-20	16	others
B	B02	01-Feb-20	49	others
B	B03	01-Feb-20	27	others
B	B04	01-Feb-20	42	others
C	C01	01-Feb-20	56	others
C	C02	01-Feb-20	11	others
C	C03	01-Feb-20	13	others
C	C04	01-Feb-20	30	others
A	A01	01-Mar-20	31	A
A	A02	01-Mar-20	23	A
A	A03	01-Mar-20	44	A
A	A04	01-Mar-20	29	A
B	B01	01-Mar-20	24	others
B	B02	01-Mar-20	11	others
B	B03	01-Mar-20	11	others
B	B04	01-Mar-20	24	others
C	C01	01-Mar-20	15	others
C	C02	01-Mar-20	53	others
C	C03	01-Mar-20	58	others
C	C04	01-Mar-20	18	others
A	A01	01-Apr-20	47	A
A	A02	01-Apr-20	27	A
A	A03	01-Apr-20	27	A
A	A04	01-Apr-20	44	A
B	B01	01-Apr-20	49	others
B	B02	01-Apr-20	37	others
B	B03	01-Apr-20	25	others
B	B04	01-Apr-20	27	others
C	C01	01-Apr-20	30	others
C	C02	01-Apr-20	33	others
C	C03	01-Apr-20	28	others
C	C04	01-Apr-20	19	others
A	A01	01-May-20	34	A
A	A02	01-May-20	52	A
A	A03	01-May-20	50	A
A	A04	01-May-20	27	A
B	B01	01-May-20	55	others
B	B02	01-May-20	39	others
B	B03	01-May-20	15	others
B	B04	01-May-20	26	others
C	C01	01-May-20	52	others
C	C02	01-May-20	13	others
C	C03	01-May-20	14	others
C	C04	01-May-20	35	others

View solution in original post

v-rongtiep-msft · ‎11-23-2022

Hi @kompalli ,

I have created a simple smaple, please refer to it to see if it helps you.

ColumnA = VAR _1= RANKX(FILTER('Table','Table'[company]="A"&&'Table'[DATE]=EARLIER('Table'[DATE])),'Table'[sales],,DESC,Dense)
RETURN
IF(_1<=5&&'Table'[company]="A",_1,BLANK())

ColumnNOA = VAR _1= RANKX(FILTER('Table','Table'[company]<>"A"&&'Table'[DATE]=EARLIER('Table'[DATE])),'Table'[sales],,DESC,Dense)
RETURN
IF(_1<=5&&'Table'[company]<>"A",_1,BLANK())

If it still does not help you, please peovide more details to explain: Company A, date 2020/2/1, the A02 's rankx is 2. There are 50(A01) ,43( A03) ,48(A06) ,40(A07) bigger than it.

It seems other company rankx by date. Why 42(B04) is 1?

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

v-rongtiep-msft · ‎11-17-2022

Hi @kompalli ,

I have created a simple sample, please reer to it to see if ithelps you.

Create a column.

RANKX = VAR _1= RANKX(FILTER('Table','Table'[company ]=EARLIER('Table'[company ])),'Table'[sales],,DESC,Skip)
RETURN
IF(_1<=5,_1,BLANK())

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output with more details.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

kompalli · ‎11-18-2022

Hi @v-rongtiep-msft ,

Thankyou for responding with a sample file. I tried to share the .pbix file for clear understanding of requirement but I am unable to share it.

v-rongtiep-msft · ‎11-20-2022

Hi @kompalli ,

Please refer to .

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

How to Get Your Question Answered Quickly

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

kompalli · ‎11-21-2022

Hi @v-rongtiep-msft

Below is the sample data and I want to create calculated column based ranking as shown in the sample table "RANK" column. The Ranking should be done seperately based on the company A and others .

Thankyou in advance

Company	product	Sales	A and others	Date	Rank	YYYY MMM
A	A01	15	A	01-Jan-20	10	2020 Jan
A	A02	16	A	01-Jan-20	9	2020 Jan
A	A03	17	A	01-Jan-20	8	2020 Jan
A	A04	18	A	01-Feb-20	7	2020 Feb
A	A05	19	A	01-Feb-20	6	2020 Feb
A	A06	20	A	01-Feb-20	5	2020 Feb
A	A07	21	A	01-Mar-20	4	2020 Mar
A	A08	22	A	01-Mar-20	3	2020 Mar
A	A09	23	A	01-Mar-20	2	2020 Mar
A	A10	24	A	01-Apr-20	1	2020 Apr
B	B01	30	others	01-Apr-20	9	2020 Apr
B	B02	29	others	01-Apr-20	10	2020 Apr
B	B03	28	others	01-May-20	11	2020 May
B	B04	27	others	01-May-20	12	2020 May
B	B05	26	others	01-May-20	13	2020 May
B	B06	25	others	01-Jun-20	14	2020 Jun
B	B07	24	others	01-Jun-20	15	2020 Jun
B	B08	23	others	01-Jun-20	16	2020 Jun
B	B09	22	others	01-Jul-20	17	2020 Jul
B	B10	21	others	01-Jul-20	18	2020 Jul
C	C01	50	others	01-Aug-20	1	2020 Aug
C	C02	49	others	01-Aug-20	2	2020 Aug
C	C03	48	others	01-Aug-20	3	2020 Aug
C	C04	47	others	01-Sep-20	4	2020 Sep
C	C05	46	others	01-Sep-20	5	2020 Sep
C	C06	49	others	01-Oct-20	2	2020 Oct
C	C07	48	others	01-Nov-20	3	2020 Nov
C	C08	43	others	01-Nov-20	6	2020 Nov
C	C09	42	others	01-Dec-20	7	2020 Dec
C	C10	41	others	01-Jan-21	8	2020 Dec

v-rongtiep-msft · ‎11-21-2022

Hi @kompalli ,

Please refer to my pbix file to see if it helps you.

Create a column.

Column = var _1= RANKX(FILTER(ALL('Table'),'Table'[A and others]=EARLIER('Table'[A and others])),'Table'[Sales],,DESC,Dense)
return
IF(_1<=5,_1,BLANK())

Then filter the line chart: Column is not blank.

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output with more details.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

kompalli · ‎11-22-2022

Hi @v-rongtiep-msft Thanks for the quick response. It worked but even product column should be part of ranking. So the ranking is done based on company,product column as well as sales. These three conditions into one dax to create calculated ranking column. Could please suggest an alternative where a sales measure can be added to the calculated ranking column.

v-rongtiep-msft · ‎11-22-2022

Hi @kompalli ,

"So the ranking is done based on company,product column as well as sales." How to rank based on different product column? What is the result like? "an alternative where a sales measure can be added to the calculated ranking column." How the measure like ? Could you please provide more details like the Message 6? Please list all the outputs you want.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

kompalli · ‎11-23-2022

Hi @v-rongtiep-msft ,

Thanks for your response. Below are the measures for your reference that I have used to rank company "A" and "others" based on product, company and sales measures. It was working fine but when used as measure, the same logic I couldn't apply it to a calculated column.

Rank by A Sales = if (SELECTEDVALUE (Table name [Company]) ="A”, RANKX (

SUMMARIZE (ALL (Table name), Table name [Product], Table name [Company]), [Sales measure], CALCULATE ([Sales measure], Table name [company] = "A"), DESC, Dense))

Rank by others Sales = if (SELECTEDVALUE (Table name [Company]) ="others”, RANKX (SUMMARIZE (ALL (Table name), Table name [Product], Table name [company), [Sales measure], CALCULATE ([Sales measure], Table name [company] = "others"), DESC, Dense))

These are the measures used to rank based on company “A” and “others” but based on these conditions a calculated column is required. I tried doing it but I couldn't get the desired ranking.

Below image is that shows the expected output in detail.
There might be similar products repeating for a company over a period based on sales.

Below is the sample data for your reference.

Company	Product	Date	Sales	A and others
A	A01	01-Feb-20	50	A
A	A02	01-Feb-20	36	A
A	A03	01-Feb-20	43	A
A	A04	01-Feb-20	31	A
B	B01	01-Feb-20	16	others
B	B02	01-Feb-20	49	others
B	B03	01-Feb-20	27	others
B	B04	01-Feb-20	42	others
C	C01	01-Feb-20	56	others
C	C02	01-Feb-20	11	others
C	C03	01-Feb-20	13	others
C	C04	01-Feb-20	30	others
A	A01	01-Mar-20	31	A
A	A02	01-Mar-20	23	A
A	A03	01-Mar-20	44	A
A	A04	01-Mar-20	29	A
B	B01	01-Mar-20	24	others
B	B02	01-Mar-20	11	others
B	B03	01-Mar-20	11	others
B	B04	01-Mar-20	24	others
C	C01	01-Mar-20	15	others
C	C02	01-Mar-20	53	others
C	C03	01-Mar-20	58	others
C	C04	01-Mar-20	18	others
A	A01	01-Apr-20	47	A
A	A02	01-Apr-20	27	A
A	A03	01-Apr-20	27	A
A	A04	01-Apr-20	44	A
B	B01	01-Apr-20	49	others
B	B02	01-Apr-20	37	others
B	B03	01-Apr-20	25	others
B	B04	01-Apr-20	27	others
C	C01	01-Apr-20	30	others
C	C02	01-Apr-20	33	others
C	C03	01-Apr-20	28	others
C	C04	01-Apr-20	19	others
A	A01	01-May-20	34	A
A	A02	01-May-20	52	A
A	A03	01-May-20	50	A
A	A04	01-May-20	27	A
B	B01	01-May-20	55	others
B	B02	01-May-20	39	others
B	B03	01-May-20	15	others
B	B04	01-May-20	26	others
C	C01	01-May-20	52	others
C	C02	01-May-20	13	others
C	C03	01-May-20	14	others
C	C04	01-May-20	35	others

v-rongtiep-msft · ‎11-23-2022

Hi @kompalli ,

I have created a simple smaple, please refer to it to see if it helps you.

ColumnA = VAR _1= RANKX(FILTER('Table','Table'[company]="A"&&'Table'[DATE]=EARLIER('Table'[DATE])),'Table'[sales],,DESC,Dense)
RETURN
IF(_1<=5&&'Table'[company]="A",_1,BLANK())

ColumnNOA = VAR _1= RANKX(FILTER('Table','Table'[company]<>"A"&&'Table'[DATE]=EARLIER('Table'[DATE])),'Table'[sales],,DESC,Dense)
RETURN
IF(_1<=5&&'Table'[company]<>"A",_1,BLANK())

If it still does not help you, please peovide more details to explain: Company A, date 2020/2/1, the A02 's rankx is 2. There are 50(A01) ,43( A03) ,48(A06) ,40(A07) bigger than it.

It seems other company rankx by date. Why 42(B04) is 1?

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.