Forum Discussion
How to get multiple category lines with bar chart?
Sample Data.
| CompanyName | Date | SaleAmount |
| CompanyA | 2019-01-01 | 29000 |
| CompanyA | 2019-02-01 | 35000 |
| CompanyA | 2019-03-01 | 43000 |
| CompanyA | 2019-04-01 | 27000 |
| CompanyA | 2019-05-01 | 45000 |
| CompanyA | 2019-06-01 | 21000 |
| CompanyA | 2019-07-01 | 26000 |
| CompanyA | 2019-08-01 | 27285 |
| CompanyA | 2019-09-01 | 26035 |
| CompanyA | 2019-10-01 | 24785 |
| CompanyA | 2019-11-01 | 23535 |
| CompanyA | 2019-12-01 | 22285 |
Expected Output.
My Boss has asked me to show sale trend by companies and month. but he wants it to be shown like below.
1. X axis should have Month Names
2. Bar lines should tell overall sales amount by month.
3. multiple lines should represent company sales amount.
i have achived this output by replacing 2 charts on each other (hidden one background).
First i have taken a CLUSTERED COLUMN CHART and place a Line chart on it with hidden background.
But for me this is just an adhoc solution and One more problem is that both chart's y axis doesn't match.
seeking for a good solution on this. either another good chart to represent this kind of data or how to achive it any other way?
Thanks in advance.
Hi Anonymous
One option can be to use a line chart with two measures for this you also will need to create a new table on your model see the steps below:
- Create a new table with Company + a line with others:
Company + Others = UNION ( DISTINCT ( Sales[CompanyName] ); ROW ( "CompanyName"; "Total" ) )- Create the following measure:
Measure 2 = VAR selectedCategories = ALL ( Sales[CompanyName] ) RETURN SWITCH ( SELECTEDVALUE ( 'Company + Others'[CompanyName] ); "Total"; CALCULATE ( SUM ( Sales[SaleAmount] ); 'Company + Others'[CompanyName] IN selectedCategories ); CALCULATE ( SUM ( Sales[SaleAmount] ); FILTER ( ALLSELECTED ( Sales[CompanyName] ); Sales[CompanyName] = SELECTEDVALUE ( 'Company + Others'[CompanyName] ) ) ) )This measure calculate the total sales based on selected companies if you want to have a line with all the companie no matter what is the slicer of the company use the measure below:
Total_Sales_ALL = VAR selectedCategories = ALL ( Sales[CompanyName] ) RETURN SWITCH ( SELECTEDVALUE ( 'Company + Others'[CompanyName] ); "Total"; CALCULATE ( SUM ( Sales[SaleAmount] ); ALL(Sales[CompanyName]) ); CALCULATE ( SUM ( Sales[SaleAmount] ); FILTER ( ALLSELECTED ( Sales[CompanyName] ); Sales[CompanyName] = SELECTEDVALUE ( 'Company + Others'[CompanyName] ) ) ) )- Create a line chart with the following format:
- Axis: Date[Month]
- Legend: Company+Others[CompanyName]
- Values: [Total_Sales] or [Total_Sales_All]
- Shape:
- Customize series:
- Select Total
- Turn on Stepped.
- Create a slicer with the CompanyName from the sales table. (optional)
Be aware that making this in this way selecting a company in another visualization (not necessarily a slicer will make a filtering of the line chart so is dinamic)
See result below and in attach PBIX file in the left is total only for the selected values on the rigth is the total for all companies.
6 Replies
- MFelix
Super User
Hi Anonymous,
Depending on the number of companies and if you need to have it dinamic or not believe that the best option is to create a measure for each of the values you need so a measure for each line and another for the total.
You may need to have some filtering if you want to have selection of the companies but as referred if there are many companies this can be difficult to implement.
hi MFelix Thanks for your reply.
I've almost 2k compnies in Company Column.
I had share few comnies data for sample.
Is there any way to do it dynamically ? or any other solution- MFelix
Hi Anonymous
One option can be to use a line chart with two measures for this you also will need to create a new table on your model see the steps below:
- Create a new table with Company + a line with others:
Company + Others = UNION ( DISTINCT ( Sales[CompanyName] ); ROW ( "CompanyName"; "Total" ) )- Create the following measure:
Measure 2 = VAR selectedCategories = ALL ( Sales[CompanyName] ) RETURN SWITCH ( SELECTEDVALUE ( 'Company + Others'[CompanyName] ); "Total"; CALCULATE ( SUM ( Sales[SaleAmount] ); 'Company + Others'[CompanyName] IN selectedCategories ); CALCULATE ( SUM ( Sales[SaleAmount] ); FILTER ( ALLSELECTED ( Sales[CompanyName] ); Sales[CompanyName] = SELECTEDVALUE ( 'Company + Others'[CompanyName] ) ) ) )This measure calculate the total sales based on selected companies if you want to have a line with all the companie no matter what is the slicer of the company use the measure below:
Total_Sales_ALL = VAR selectedCategories = ALL ( Sales[CompanyName] ) RETURN SWITCH ( SELECTEDVALUE ( 'Company + Others'[CompanyName] ); "Total"; CALCULATE ( SUM ( Sales[SaleAmount] ); ALL(Sales[CompanyName]) ); CALCULATE ( SUM ( Sales[SaleAmount] ); FILTER ( ALLSELECTED ( Sales[CompanyName] ); Sales[CompanyName] = SELECTEDVALUE ( 'Company + Others'[CompanyName] ) ) ) )- Create a line chart with the following format:
- Axis: Date[Month]
- Legend: Company+Others[CompanyName]
- Values: [Total_Sales] or [Total_Sales_All]
- Shape:
- Customize series:
- Select Total
- Turn on Stepped.
- Create a slicer with the CompanyName from the sales table. (optional)
Be aware that making this in this way selecting a company in another visualization (not necessarily a slicer will make a filtering of the line chart so is dinamic)
See result below and in attach PBIX file in the left is total only for the selected values on the rigth is the total for all companies.
- v-eachen-msft
Community Support
Hi Anonymous ,
You could try line and clustered column chart.
hi v-eachen-msft Thanks for your reply !
This solution i already showed my boss. but he not quite satisfied with the visual. He said this looks so cluttered.
Any other visual or is there any other way around get the multiple lines with bar chart?
- MFelix
Hi Anonymous ,
I have prepared a sample file based on the file you share see if this works for your purpose, be aware that as refered you need to have a measure for each company.
