Line chart from Excel always shows flat line / bar for one table, summing up all values

[NCS]

Hi,

Apologies if this has been covered before, I read many posts, but none had quite the same scenario.

Using Power BI Online, I want to compare download numbers for an app, as well as a comparison of won / lost users, so a (stacked) line chart seems best. The data will always be for the constant stream of date and then varying values per OS. My data comes from an Excel, I have tried data (date in column a, numbers in column B) in one worksheet or distributed over two worksheets, the problem remains the same.

The first set of values is displayed as desired, a zig-zag line on the Y axis depending on the value for a date on the X axis. However, the second set of values always sums up all values for the available dates, creating a flat line (or bar in a bar chart). Why is this happening? A stacked area chart with the exact same data works, I don't get it -.-

Any help is appreciated - this seems fairly straightforward, although it's my first attempt with Power BI. Thanks!

[123abc]

It sounds like you're trying to create a stacked line chart in Power BI Online to compare download numbers for an app and the won/lost users over time, but you're encountering an issue where the second set of values is not being displayed as expected and is instead creating a flat line. This issue can occur due to the way the data is structured or how the chart is configured.

Here are some steps to troubleshoot and potentially resolve the issue:

1. Data Structure: Ensure that your data is properly structured. It should have two columns: one for the date (X-axis) and another for the values (Y-axis) you want to display. If you have two sets of values (downloads and won/lost users), you should have three columns in your data: Date, Downloads, and Won/Lost Users.

2. Data Type: Make sure that the date column is recognized as a date data type in Power BI. You can check this in the Power Query Editor or by selecting the date column in the Fields pane and setting its data type to Date.

3. Visualization Setup:

   • Drag the Date column to the Axis field well to set it as the X-axis.
   • Drag the Downloads and Won/Lost Users columns to the Values field well.
   • In the Values field well, make sure the aggregation for both Downloads and Won/Lost Users is set to "Sum" or the appropriate aggregation method based on your data.

4. Legend and Stacking: Ensure that the Legend field well is empty, as you don't need to split the data by any category. The stacking should be set to "Stacked" for both Downloads and Won/Lost Users. You can set this in the "Format" pane under the "Data labels" section.

5. Check Data Quality: Verify that there are no missing or inconsistent values in your data. Sometimes, flat lines can be the result of missing data points.

6. Visual Interaction Filters: Make sure that there are no filters applied at the report level that could be affecting the chart's behavior.

7. Data Model: If you're importing data from multiple worksheets in Excel, ensure that the data from both worksheets is properly merged in the Power Query Editor and forms a single, coherent data table.

8. Chart Type: While you mentioned that a stacked area chart works, you might consider using a stacked column chart or clustered column chart to better represent the data if it's not displaying as expected with a stacked line chart.

If you've followed these steps and are still experiencing issues, it might be helpful to share a bit more detail about your data and the exact settings you're using in Power BI to better diagnose the problem. Additionally, consider checking for any updates or patches for Power BI Online, as software updates can sometimes resolve unexpected behavior.

[NCS]

Thank you for your detailed reply! I checked all the points, and it did give me some insights even if it did not resolve the issue - as the problem was that my data is in two tables and no connection in between exists.

[v-binbinyu-msft · Accepted solution]

Hi @NCS ,

I test it in my Power BI Desktop, it works well.

Your visualization is correct, due to the fact that your Y-axis field is using data from two different tables, and there is no relationship between these two tables.

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NCS]

Hi Binbin Yu,

Thank you so much! I had no idea that the data must be in the same table in order to relate to each other the way it does in a line or bar chart! If I use three columns (date, downloads Android, downloads iOS) in one table, it works like a charm. Same for win/loss, one table per OS with the same structure and then two charts.