Stacked Line Chart - not stacking up

orensteinb — Tue, 20 Aug 2024 15:34:02 GMT

Hello, I am relatively experienced with power BI but have recently been tasked with creating a visual representation of a company's revenue over time, by entity. As you can see from the examples below, the comany has a lot of different entities - this is not the issue. I'm trying to make all of the lines in the chart stack up - the only problem is, some entities will have no recorded revenue at all in a given period (like the data set doesn't show zero revenue, it doesn't have a value at all in that period). Additionally, some entities will have a negative revenue in a given period. What I would like to see is that the stacked lines all stack up each period, but I would like to see none of the lines cross each other. That way it is super clear that in any given period, which entities have which amount of revenue. I know that using a stacked bar chart accomplishes this very easily, but was curious if there is a way to make it work for line charts.

I have included two screenshots of the charts - after doing some searching online someone suggested to switch the x-axis type to categorical instead of continuous but they both showed the lines crossing each other.

X-axis as continuous:

x-axis as categorical:

Thank you!

Re: Stacked Line Chart - not stacking up

Anonymous — Wed, 21 Aug 2024 06:23:35 GMT

Hi, @orensteinb .

Regarding the issue you raised, my solution is as follows:

First I have created the following table and the column names and data are the data you have given:

For example, I need the value of column to stay above column2 and the two lines do not intersect:
1.I've created a measure substitution visualization like this:

Measure = IF(SUM('Table'[Column])>=SUM('Table'[Column 2]),SUM('Table'[Column]),0)

Measure 2 = IF(SUM('Table'[Column 2])<SUM('Table'[Column]),SUM('Table'[Column 2]),0)

2.Then modify the filter:

3.Here's the original visualization:

Here's a visualization that uses a measure judgment:

If your needs involve multiple judgments, you can also choose the switch() function, and here is the documentation:
SWITCH function (DAX) - DAX | Microsoft Learn

Of course, you can also use PowerQuery to modify the data itself.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Stacked Line Chart - not stacking up in Developer

Stacked Line Chart - not stacking up

Re: Stacked Line Chart - not stacking up