cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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?

1 ACCEPTED SOLUTION
Super User

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.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

6 REPLIES 6
Super User

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.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Community Support

Hi @Anonymous ,

You could try line and clustered column chart.

If this post helps, then please consider Accept it as the solution to help the other members find it.
Anonymous
Not applicable

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?

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.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

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

Super User

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.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.