Forum Discussion
Top 3 Competitor Sales
How to display Top 3 competitor sales along with Nike Sales in Bar chart based on Type selection. Is possible to show different colors for different companies.
| Type | Company | Sales | Competitor |
| Shoes | Nike | 40 | No |
| Shoes | Addidas | 50 | Yes |
| Shoes | Puma | 30 | Yes |
| Shoes | Hoka | 20 | Yes |
| Shoes | UnderArmor | 10 | Yes |
| Shoes | Ascis | 5 | Yes |
| Hiking | Nike | 10 | No |
| HIking | Addidas | 40 | Yes |
| Hiking | Puma | 20 | Yes |
| Hiking | Hoka | 50 | Yes |
| Hiking | UnderArmor | 30 | Yes |
| Activeware | NIke | 120 | No |
| Activeware | Addidas | 30 | Yes |
| Activeware | Puma | 50 | Yes |
| Activeware | Hoka | 60 | Yes |
| Activeware | UnderArmor | 100 | Yes |
| Activeware | Ascis | 70 | Yes |
Hi,
I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.
In my pbix file, in the measure, top 3 selection is based on the all sales of the selected type. For instance, if one type is selected, top3 is based on the sale of one type. If two types are selected, top3 is based on the sales of all-two-types.
And then, I create one more measure to define the color in the barchart.
Please check the below picture and the attached pbix file.
WINDOW function (DAX) - DAX | Microsoft Learn
Top 3 competitor with my company: = VAR _mycompany = CALCULATE ( SUM ( Data[Sales] ), KEEPFILTERS ( Company[Competitor] = "No" ) ) VAR _competitors = FILTER ( ALL ( Company ), Company[Competitor] = "Yes" ) VAR _top3competitors = SUMMARIZE ( WINDOW ( 1, ABS, 3, ABS, _competitors, ORDERBY ( CALCULATE ( SUM ( Data[Sales] ), ALLSELECTED ( 'Type'[Type] ) ), DESC ) ), Company[Company] ) VAR _salescompetitors = CALCULATE ( SUM ( Data[Sales] ), KEEPFILTERS ( Company[Company] IN _top3competitors ) ) RETURN _salescompetitors + _mycompanycolor: = MAX( Company[Color] )
2 Replies
Hi,
I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.
In my pbix file, in the measure, top 3 selection is based on the all sales of the selected type. For instance, if one type is selected, top3 is based on the sale of one type. If two types are selected, top3 is based on the sales of all-two-types.
And then, I create one more measure to define the color in the barchart.
Please check the below picture and the attached pbix file.
WINDOW function (DAX) - DAX | Microsoft Learn
Top 3 competitor with my company: = VAR _mycompany = CALCULATE ( SUM ( Data[Sales] ), KEEPFILTERS ( Company[Competitor] = "No" ) ) VAR _competitors = FILTER ( ALL ( Company ), Company[Competitor] = "Yes" ) VAR _top3competitors = SUMMARIZE ( WINDOW ( 1, ABS, 3, ABS, _competitors, ORDERBY ( CALCULATE ( SUM ( Data[Sales] ), ALLSELECTED ( 'Type'[Type] ) ), DESC ) ), Company[Company] ) VAR _salescompetitors = CALCULATE ( SUM ( Data[Sales] ), KEEPFILTERS ( Company[Company] IN _top3competitors ) ) RETURN _salescompetitors + _mycompanycolor: = MAX( Company[Color] )hello kanth123
i think you can do something like this.
1. create a new measure for sorting top 3 highest sales
Rank =
RANKX(
FILTER(
ALL('Table'),
'Table'[Company]=SELECTEDVALUE('Table'[Company])&&
'Table'[Competitor]=SELECTEDVALUE('Table'[Competitor])
),
CALCULATE(MAX('Table'[Sales])),,
DESC,
Dense
)2. plot in table visual then use visual filter to remove rank above 3.for coloring different company, i think conditional formating will do the job.
Hope this will help.
Thank you.
