Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Anonymous
Not applicable

How to get multiple category lines with bar chart?

Sample Data.

 

CompanyNameDateSaleAmount
CompanyA2019-01-0129000
CompanyA2019-02-0135000
CompanyA2019-03-0143000
CompanyA2019-04-0127000
CompanyA2019-05-0145000
CompanyA2019-06-0121000
CompanyA2019-07-0126000
CompanyA2019-08-0127285
CompanyA2019-09-0126035
CompanyA2019-10-0124785
CompanyA2019-11-0123535
CompanyA2019-12-0122285

 

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.

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

 

Dinamic_lines.gif


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
MFelix
Super User
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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



v-eachen-msft
Community Support
Community Support

Hi @Anonymous ,

 

You could try line and clustered column chart.

2-1.PNG

2-2.PNG

 

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

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
Super User
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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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 

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.

 

Dinamic_lines.gif


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.