How to get multiple category lines with bar chart?

[Anonymous]

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.

Sample File 

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.

[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.

[v-eachen-msft]

Hi @Anonymous ,

You could try line and clustered column chart.

[Anonymous]

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,

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. 

[Anonymous]

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 · Accepted solution]

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.