Line chart in power bi

Line chart in power bi DEFAULT

Dynamic Line Chart in PowerBI

A Dynamic Line Chart is very helpful where you’d want the user to have the ability to choose between multiple calculations to display in the visual. In this blog, I will discuss how to create a Dynamic Line Chart in Power BI.

Let’s start.

 

Video – Dynamic Line Chart in PowerBI

 

Consider this Line and Clustered Column Chart

The Chart has 2 two data points,

  • The Column represents the Total Sales
  • The Line represents Total Units Sold.

 

Dynamic Line chart -Line and clustered column chart

Now let’s say.. I want the user to select either Commission or Units Sold to display as a Line Value in the above Chart. And based on the selection the line chart should get updated.

In order to make this happen, we need two things.

  1. A Slicer to select one of the calculations (Commission or Units Sold)
  2. Connecting the user’s selected calculation to the Line Visual

Let’s see how to tackle each of them..

 

#1 Creating a Slicer to select between Calculations.

To be able to create a slicer I created a simple Table using the following DAX that contains the list of Calculations / Measures.

Line Chart Measures =  { "Total Units", "Commission" }

Using the column of this table, I then create a slicer.

Dynamic Line Chart in PowerBI - slicer

 

#2 Connecting the user’s selected calculation to the Line Visual

I can use a simple SWITCH & SELECTEDVALUE Function to toggle between the user’s selection and perform the calculation selected. Consider this simple DAX Measure

Selected Line = SWITCH (TRUE (), SELECTEDVALUE ( 'Line Chart Measures'[Value] ) = "Total Units", [Total Units], SELECTEDVALUE ( 'Line Chart Measures'[Value] ) = "Commission", [Commission] )

I then drag this measure into Line values of the my Visual.

Dynamic Line Chart in PowerBI - Measure drag

 

Dynamic Line Chart in Power BI

Now if the user toggles between Commission or Total Units, the relevant calculation is displayed in the Line Chart! Sweet!

Dynamic Line Chart in PowerBI - final

 

 

A few more Visual Tricks

  1. Select a Measure using a Slicer
  2. Switch between Current Year and YTD Calculation
  3. Show Measures on the Column of a Matrix Visual
  4. Automatically Set Equal width for a Matrix Visual
  5. Use Icons in Power BI
  6. Improve the Look n Feel of your Tables and Matrix Visuals

 

Learn DAX From Scratch!

Tagged In Power BI, Dashboards & Visualizations, Excel Charting Tips.

Topics that I write about...






Download Smart Ebooks on
Excel and Power BI




Sours: https://www.goodly.co.in/dynamic-line-chart-powerbi/
Org Chart

In this Power bi tutorial, we will learn What is Power Bi line chart, how to create a line chart in Power BI, and also we will learn

  • How to create a power Bi line chart?
  • Power Bi line chart month and year
  • Power Bi line chart multiple lines
  • Power Bi line chart multiple values
  • Power Bi line chart show zero values
  • Power Bi line chart marker color based on the value
  • Power Bi line chart secondary value
  • Power Bi line chart secondary axis

What is Power Bi line chart?

In Power Bi line chart is a series of data points represented by dots, connected by a straight line.

Line chart in power bi having one or many lines. It is having an X-axis and Y-axis. In Power Bi, the line chart is usually used to visualize data over time.

Read: How to create Power BI report from SharePoint list and Power Bi Bar Chart.

Complete SharePoint Training Course Just for $199 (Just for Today)

How to create a Power Bi Line chart?

Here we will see how to create a line chart in power bi desktop.

  • Open your Power Bi desktop
  • Upload data from the Get Data in Power Bi. Here we are using the Sales Data. and you can download the data from here.
Sales data in excel sheet
  • Go to the report on Power bi desktop.
  • Then select a line chart from the visualization pane
line chart in power bi desktop
  • Then in the field formatting section in the visualization pane, under Axis select the Date->Month, and in the value-> Sales.
field formatting in power bi

Now you can see the Line chart visualization in Power BI on the basis of Sales by month.

power bi line chart

This is how to create a Power Bi Line chart.

Read: Power BI Pie Chart

Power Bi line chart month and year

Here we will see how to create line chart visualization using month and year in Power Bi. We will use the above data to create line chart visualization.

To create a line chart visualization, open the Power bi Desktop and load the data. Then

  • Select the Line Chart, from the visualization in Power Bi.
  • From the Fields formatting section, drag the Order Date and put it on the Axis.
  • Then from the Date hierarchy select the Year and Month in the Field pane.
  • In the value field, Select the units delivered from the Fields pane.
power bi line chart by month and year
  • Then go to Format Tab, Click on the X-axis.
  • The X-axis is divided into two types
    • Continuous: The Continuous axis is only applicable to numeric Data, like date and time. If we are using a continuous axis, then we don’t have control over the scale, which means if we are extending the date range or increasing the size of the chart, the scale automatically adjusts.
    • Categorical: The Categorical axis can use text or number, and also it levels every data point.
  • Select the Continuous X-axis under the Type.
how to change x axis to continuous in power bi
  • Now click on the Fork icon present at the top of the Line chart visualization in the Power bi.
  • By clicking on the Fork icon, it will take one level down in the hierarchy.
  • That means here in the date hierarchy, it will first show the year, as we select the month and year for this visualization, once you click, it will go one level down in the date hierarchy and it will show month and year in the X-axis.
drill down in power bi

Now you can see the Power Bi line chart month and year having continuous X-axis.

Power Bi line chart month and year
  • Now we will see the Power Bi line chart month and year having categorical X-axis.
  • In the visualization pane-> Format section->X-axis-> Type-> Categorical.
  • In the below screenshot, you can see the Power Bi line chart month and year having categorical X-axis.
Power Bi line chart month and year having categorical X-axis

Read: How to Embed Power BI Report in SharePoint Online

Power Bi line chart multiple lines

Now we will see how to add multiple lines to Power Bi line chart using the above data.

  • Select the Power Bi Line chart from the visualization.
  • Then follow the above Power Bi line chart month and year to add the Date to the X-axis.
  • Then in the value -> profit and units delivered. This will show two lines one for the profit and another for the units delivered. Also, you can add more values in the value field to display the multiple lines in a line chart.
Power Bi line chart multiple lines

Now you can see the Power bi Line chart multiple lines based on profit and unit delivered by year and month.

power bi line chart multiples lines

Read: How to Create a Dashboard in Power Bi?

Power Bi line chart multiple values

Here we will see how to add multiple values in line chart by using the above data.

  • Select the line chart from the visualization.
  • As we do in the power Bi multiple lines, in the same way, we will add multiple values i.e Units Delivered, profit, and discount -> value and order date -> Axis.
Power Bi line chart multiple values
  • Now you can see the Power Bi line chart multiple values based on units delivered, profit, and discounts by Month.
Power Bi line chart multiple values

Power Bi line chart show zero values

Here we will see, how to show zero value in Power Bi line chart using the above data set.

  • Select the Line chart from the Visualization in Power Bi.
  • We will use the Product name -> Axis field and the Discount -> value field.
  • In the discount data column in the excel sheet, no data means “null” and zero means 0.
  • Now you can see the line chart when the X-axis is categorical.
Discount by product name in the Line chart

In the above screenshot, zero value is not shown in the Y-axis.

To solve this problem we will create a measure i.e.

According to your data, you can change your measure.

Then change the value field-> total discount.

After applying the measure, now you can see the zero value shown in the Y-axis of the Power Bi line chart.

power bi line chart show zero value.

Read: Microsoft Power bi report vs dashboard

Power Bi line chart marker color based on value

Here we will see how to create a Power Bi line chart marker color based on value by using the above excel data.

There is no straightforward option to create the Power Bi line chart marker color based on the value, we can do that by using the stacked column chart conditional formatting.

  • Select stacked column chart from the visualization pane in Power Bi.
  • Select the order date -> Axis and Quantity -> value in the field section.
Stacked column chart visualization
  • Select the Format -> Data colors in the visualization pane.
  • Then click onthe fx icon in the Default color section to apply conditional formatting.
conditional formatting power bi
  • Under Format by, select Rules.
power bi conditional formatting based on rules
  • Under Based on Field, Select the Quantity.
power bi conditional formatting based on field value
  • Then we will add rules to format the data colors in a stacked column chart visualization
  • If the value is greater than 0 and is less than 80, then select a color from the color pane.
  • Click on the New Rule to add another rule.
adding a new rule to the data colors column formatting
  • If the value is greater than 20 and less than 80, then select a color from the color pane.
  • Click on OK.
power bi data colors conditional formatting

Now you can see marker color based on the value in stacked column chart.

power bi marker color based on the value in column chart

Now convert the stacked column chart into Line chart.

In the below screenshot you can see the Power BI line chart marker color based on the value.

Power Bi Line chart marker color based on the value

Power Bi line chart secondary value

Here we will see how to get Power Bi line chart secondary value.

  • Click on the line chart from the visualization pane in Power bi desktop
  • In the Field format section, Axis-> Order date, value-> Quantity, Secondary values-> profit.
secondary values power bi
  • Now you can see the power bi line chart secondary value in right side if the Y-axis
power bi line chart secondary value

Read: How to create Power BI report from SharePoint list

Power Bi line chart secondary axis

Here we will see about the Power Bi line chart secondary axis.

  • Click on the line chart from the visualization pane in Power bi desktop.
  • In the Field format section in visualization pane, Order date->Axis, Quantity-> values, and profit->Secondary values.
secondary values power bi
  • Then in the Format section in turn on the secondary axis.
  • In the secondary axis setting, you can set the start range and end range
  • Then we can set the Scale type
  • You can set the color, text size, and font family of the Secondary axis.
Secondary axis in Power bi
  • You can set the display units of the secondary axis.
  • Under the Title section, you can set the title of the secondary axis
  • Under Style, you can set the style the title of the secondary axis.
  • You can set the Title color, Axis title, title color, title text size, and title font family.
power bi secondary y axis title

Now you can see the Power Bi line chart secondary axis based on Quantity and Profit by year and month.

Power Bi line chart secondary axis

You may also like:

In this Power Bi tutorial, we learned What is Power Bi line chart. And also we discussed how to create a Power Bi line chart?

By using the sample data we created:

  • Power Bi line chart month and year
  • Power Bi line chart multiple lines
  • Power Bi line chart multiple values
  • Power Bi line chart multiple values and legend
  • Power Bi line chart show zero values
  • Power Bi line chart marker color based on the value
  • Power Bi line chart secondary value
  • Power Bi line chart secondary axis
310f1879cff65a2b2a90b60b14dfce79?s=100&r=g

Bijay Kumar

I am Bijay from Odisha, India. Currently working in my own venture TSInfo Technologies in Bangalore, India. I am Microsoft Office Servers and Services (SharePoint) MVP (5 times). I works in SharePoint 2016/2013/2010, SharePoint Online Office 365 etc. Check out My MVP Profile.. I also run popular SharePoint web site SPGuides.com

Sours: https://www.enjoysharepoint.com/power-bi-line-chart/
  1. Shipping policy template shopify dropshipping
  2. Borderlands 3 weapons cheat sheet
  3. Chinook helicopter cost per hour

7 Secrets of the Line Chart

The line chart is the go-to chart type to visualise data over time. Typically, this visual is used to analyse your data by year, quarter, month or by day. It should be an easy thing to do to create a line chart that plots your data in the way you want. But beware, there are secrets lurking behind the benign line chart! Here are my top seven.

#1 There Are Two Different Types of X-Axis

The default x-axis, along the bottom of the chart, is what’s called a “continuous axis” and you’ll think that’s how the x-axis always has to look. But the secret is that there are actually two types of x-axis; continuous and categorical. To show the difference between these two types, let’s start with a simple line chart that uses a date column (not a date hierarchy which we look at below). In the example of a line chart below, we’ve used the SALE DATE column in the Axis bucket:-

By default, you’ll get a “continuous” axis which is only applicable to numeric data, like dates and times. You’ll see that the SALE DATE is sitting on the x-axis but we only get months and years showing, not the individual dates that are being plotted. We have no control over the scale on the continuous x-axis. For example, if we extend the date range and/or change the size of the chart, the scale automatically adjusts. Firstly, we’ll get every month, then every other month and eventually every six months:-

You’ve got no control over the format of the dates in a continuous axis. If I don’t like the short month names, too bad.

Clearly it makes sense to have the continuous x-axis as the default as it would be difficult to label every data point when you have many dates to plot. However, if you have a smaller date range e.g. a week’s worth of dates, you may want a label for every date. If this is the case, you can change the x-axis to a “categorical” axis by using this option on the X-axis card on the paint roller:-

A categorical axis can use text or number values but unlike the continuous axis, it always labels every data point.

If you’re using a categorical axis and your own date
column, unlike the continuous axis, you can format the date any way you want. You can either format the date column in your table using the “Format” button on the Column tools tab:-

Or, if you want a special format, you can create a calculated column in the table where your date is and use a DAX expression like this one:-

SALE DATE V2 = FORMAT( Sales[SALE DATE], “dd mmm yy” )

Note: the table that holds the date is called “Sales”

In the above expression, the “d”, “m” and “y” are placeholders for how you want to format the day, month and year respectively:-

You can then use your new date in the Axis bucket

Mostly however, you’ll probably be using the default date hierarchy that’s created for you by Power BI. If you’re using the default date hierarchy, you can drill down to quarter or month granularity by using “Expand all down one level in the hierarchy”.

A date hierarchy in the Axis bucket

You can drill down to different granularities

Just like when using a date column, by default you’ll be given a continuous x-axis as you drill down. But again, you can change this to a categorical axis if you prefer. For example, you can have a line chart at month granularity with either of these axes types:-

Using the default Date Hierarchy you can change the Axis type

Continuous Axis at Month Granularity

Categorical Axis at Month Granularity

So remember, there are two x-axes from which you can pick, so you can design the right line chart that is applicable to your data.

#2 When Using a Default Date Hierarchy You Can’t Change the Date Format

This is the secretive difference between using a date column and using the default hierarchy. You can’t change the format of the categorical axis if you use the hierarchy, which is a shame because when you drill down to month granularity, you’re lumbered with a long month names that just takes up space:-

If you want to use the hierarchy but to also have your own formats for year, quarter and month, you will need to create your own separate columns, and this is my next secret!

#3 How to Format Year, Quarter and Month on the X-Axis

The secret to formatting the x-axis the way you want when using a hierarchy, is to create separate columns for year, quarter, month and month number alongside your date. You can format them in the way you want and so control how they look on the x-axis:-


Note: Month number is required to sort the month names, using the “Sort by column” button.

You can use Power Query to generate these columns. You can then use these columns in the Axis bucket of the line chart, creating a hierarchy of your own that you can drill down. Use the “Expand all down one level in the hierarchy” button to drill to different granularities. At last we’ve been able to get a short year and short month names:-

You may find the line chart is sorted by the values rather by your dates. You can change this by clicking on the “More Options” button and selecting the correct sorting options:-

However, there is one drawback to creating your own hierarchy. Because the quarter and the month labels are text values, you can only plot them on a categorical axis. The secret to achieving a continuous axis for quarter and month follows….

#4 How to Get a Continuous Axis for Quarter and Month

If you’re working with you own date hierarchy and you drill from year to quarter or to month granularity, the x-axis changes from “Continuous” to “Categorical”. This is because the quarter and month columns are text values and you can only have a continuous axis with a numeric column, such as year. Also the x-axis labels are concatenated by default for the categorical axes:-

Year granularity has a continuous x-axis

Quarter or month granularity have a categorical x-axis

We’ve already seen how we can turn off the concatenation for the categorical axis but even then, it’s so annoying to always have to have every data point labelled! So what is the secret of getting a continuous axis for quarter and month?

To let you into the secret, there are two things you’ll need to do. Firstly you’ll need to have a separate date table, where you’ve created your date hierarchy. Or at least you’ll have to make sure that Power BI doesn’t create date hierarchies for you. You can turn off the automatic creation of date hierarchies in the Power BI Desktop Options, under “Data Load”:-

The second thing you’ll then need to do is to create separate columns for quarter and month that generate dates and not text. If you have your own date table (or you’ve turned off “Auto date/time for new files”) you can create these two calculated columns in addition to your Year, Qtr and Month columns. You can then use these in the Axis bucket instead of the hierarchy.

Year-Month = CALCULATE (
MIN ( DateTable[DateKey] ),
ALLEXCEPT ( DateTable, DateTable[MonthNo], DateTable[Qtr], DateTable[Year] )
)

————————————————–

Year-Quarter = CALCULATE (
MIN ( DateTable[DateKey] ),
ALLEXCEPT ( DateTable, DateTable[Qtr], DateTable[Year] )
)

A continuous axis showing month using a calculated column to generate a date

A continuous axis showing quarter using a calculated column to generate a date

#5 Turn Off Concatenation to Get a Multiple X-Axis

If you have a categorical x-axis, the default is to concatenate your labels. This is where the labels of the date hierarchy are strung together as you drill down e.g. “2018 Qtr 1 February” is Year, Qtr and Month. Often you want to turn it off because it looks very ugly and takes up too much room. You probably want a multiple x-axis like the one you get in Excel line charts:-

What is the secret to getting the line chart to look like the Excel one? Well, the answer lies in turning off the concatenated labels by using the slider on the X-Axis card:-

Concatenated Categorical X-Axis

Non-Concatenated Categorical X-Axis

Note: If turning off the “Concatenate labels” option doesn’t appear to work, here’s the secret; for some unknown reason you just need to re-sort by “Year Qtr Month” (see above) even though they are already sorted like this.

Beware that as soon as you turn off the “Concatenate Labels” option, you lose the ability to change the axis type back to Continuous. To be fair, it would be impossible to have a non-concatenated continuous axis, but it’s sometimes not obvious that this is why you can’t change the axis type.

Concatenation is on and you can change the axis type

Concatenation is off and you can’t change the axis type

#6 You Can Colour Each Data Point

It’s no secret that you can use the Shapes formatting card to turn on markers:-

But did you know that you can have each data point coloured differently? On the Data colors card, turn on the “Show all” slider and jazz up your line chart!

#7 Getting More Space on a Categorical Axis

If there’s not enough space for the labels on a categorical, concatenated axis, you can try using the “Maximum size” slider on the X-Axis card to give you more room:-

I hope you liked my seven secrets of the line chart. Would you have thought there were so many quirks to this humble visual?

Line charts in Power BI are extremely useful when it comes to displaying date information clearly and wanting to drilldown into the numbers. We cover a range of visualisations on both our Fundamentals and Advanced courses, meaning you can get in depth knowledge on exactly how to use these functions to your advantage.

If you would like more information about our courses, get in touch with our team on 0800 0199 746.

Sours: https://www.burningsuit.co.uk/blog/2020/07/7-secrets-of-the-line-chart/
line chart in power bi - Tips on using the line chart Visual in Power BI - ssu

Line charts in Power BI

APPLIES TO:Does not apply to.Power BI service for consumersApplies to.Power BI service for designers & developers Applies to.Power BI Desktop Does not apply to.Requires Pro or Premium license

A line chart is a series of data points that are represented by dots and connected by straight lines. A line chart may have one or many lines. Line charts have an X and a Y axis.

Simple line chart.

Create a line chart

These instructions use the Sales and Marketing Sample app to create a line chart that displays this year's sales by category. To follow along, get the sample app from appsource.com.

Note

Sharing your report with a Power BI colleague requires that you both have individual Power BI Pro licenses or that the report is saved in Premium capacity.

  1. Start on a blank report page. If you're using the Power BI service, make sure you open the report in Editing View.

  2. From the Fields pane, select SalesFact > Total units, and select Date > Month. Power BI creates a column chart on your report canvas.

    Select from the Fields pane.

  3. Convert to a line chart by selecting the line chart template from the Visualizations pane.

    Convert to line chart.

  4. Filter your line chart to show data for the years 2012-2014. If your Filters pane is collapsed, expand it now. From the Fields pane, select Date > Year and drag it onto the Filters pane. Drop it under the heading Filters on this visual.

    Line next to Fields pane.

    Change Advanced filtering to Basic filtering and select 2012, 2013 and 2014.

    Filter for Year.

  5. Optionally, adjust the size and color of the chart's text.

    Increase font size and change Y axis font.

Add additional lines to the chart

Line charts can have many different lines. And, in some cases, the values on the lines may be so divergent that they don't display well together. Let's look at adding additional lines to our current chart and then learn how to format our chart when the values represented by the lines are very different.

Add additional lines

Instead of looking at total units for all regions as a single line on the chart, let's split out total units by region. Add additional lines by dragging Geo > Region to the Legend well.

One line for each region.

Use two Y axes

What if you want to look at total sales and total units on the same chart? Sales numbers are so much higher than unit numbers, making the line chart unusable. In fact, the red line for total units appears to be zero.

Screenshot shows how using a single y axis displays the total units as essentially flat and a useless comparison with the sales figures.

To display highly diverging values on one chart, use a combo chart. You can learn all about combo charts by reading Combo charts in Power BI. In our example below, we can display sales and total units together on one chart by adding a second Y axis.

Screenshot shows the sales values as a bar chart with the y axis on the left and the total units as a line with the y axis on the right.

Highlighting and cross-filtering

For information about using the Filters pane, see Add a filter to a report.

Selecting a data point on a line chart cross-highlights and cross-filters the other visualizations on the report page... and vice versa. To follow along, open the Market Share tab.

On a line chart, a single data point is the intersection of a point on the X-axis and Y-axis. When you select a data point, Power BI adds markers indicating which point(for a single line) or points (if there are two or more lines) are the source for the cross-highlighting and cross-filtering of the other visuals on the report page. If your visual is very dense, Power BI will select the closest point to where you click on the visual.

In this example, we've selected a data point that encompasses: July 2014, % Units Market Share R12M of 33.16 and % Units Market Share of 34.74.

Select a single data point on a line chart.

Notice how the column chart is cross-highlighted, and the gauge is cross-filtered.

To manage how charts cross-highlight and cross-filter each other, see Visualization interactions in a Power BI report.

Considerations and troubleshooting

  • One line chart cannot have dual Y axes. You'll need to use a combo chart instead.
  • In the examples above, the charts were formatted to increase font size, change font color, add axis titles, center the chart title and legend, start both axes at zero, and more. The Formatting pane (paint roller icon) has a seemingly endless set of options for making your charts look the way you want them to. The best way to learn is to open the Formatting pane and explore.

Next steps

Visualization types in Power BI

Sours: https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-line-chart

Power in bi chart line

We took with us plenty of wine and snacks. We settled down on the grass in the shade near the house. First we went for a swim in the river, then we returned, laid out wine, food and began to drink, eat and. Chat. At first I did not notice how the girls winked at each other, I drank a lot and did not notice at all that Irka and Natasha drank very little.

5.1 How to create Line Chart in Microsoft Power BI Desktop 2020- Power BI for Beginners 2020

Yes, yes, come on, I'm yours, I'm all for you. I close eyes, I can no longer see anything, in the world I do not Far away in the mountains, on the largest peak, stood a. Castle. With a high spire, he touched the formidable clouds that covered the sky in this place.

Now discussing:

I pressed myself against the elastic hemispheres and twitched with pleasure. I lay there a little, enjoying and caressing Seryozha's elongated body with my palms. Then he began to kiss his back, gently touching his shoulder blades and protruding vertebrae with his lips. And at the same time, with indescribable pleasure, I felt my penis squeezing into the groove between the buttocks, pushing them apart, making his own way, lying vertically along the.

Groove, squeezing into it.



2137 2138 2139 2140 2141