cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

cumulative line chart in percent till 100 %

Hi Power BI Community,

line chart.PNG

I need to show a line chart with the y -axis in percent and x - axis days 
 My first quastion: How can I make the delivery days from 0 to ten days on the x - axis?

Second and bigger quastion: I want a cumilated line chart that shows when all is delivered = 100 %  like this for example but with days not units:

 

425783.image0.jpg

 



Thank you for your time!

1 ACCEPTED SOLUTION
Microsoft
Microsoft

Hi @Beginner_2020 ,

 

According to my understanding, you want to set 0-5,5-15,15-30,30-45... as X-axis ,the sum of percentage as Y-axis, and the cumulative percentage over date as the secondary Y-axis, right?

 

You could add Rank column and then switch it to create the Units column for X-axis like this:

 

Rank =
RANKX ( 'Table', [Date],, ASC, DENSE )
Units =
IF (
    [Rank] >= 1
        && [Rank] < 5,
    "0-5",
    IF (
        [Rank] >= 5
            && [Rank] < 15,
        "5-15",
        IF (
            [Rank] >= 15
                && [Rank] < 30,
            "15-30",
            IF ( [Rank] >= 30 && [Rank] < 45, "30-45" )
        )
    )
)

Here is my table output:

 

1.7.3.1.PNG

 

Then use the following formula to calculate the cumulative percentage:

Measure =
CALCULATE (
    SUM ( 'Table'[Percentage] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )
)

In addition, in order for the x-axis to be displayed in the correct order, put the Rank to Tooltip pane.

 

The final output is shown below:

1.7.3.2.PNG

Please kindly take a look at the pbix file.

 

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

 

View solution in original post

2 REPLIES 2
Microsoft
Microsoft

Hi @Beginner_2020 ,

 

According to my understanding, you want to set 0-5,5-15,15-30,30-45... as X-axis ,the sum of percentage as Y-axis, and the cumulative percentage over date as the secondary Y-axis, right?

 

You could add Rank column and then switch it to create the Units column for X-axis like this:

 

Rank =
RANKX ( 'Table', [Date],, ASC, DENSE )
Units =
IF (
    [Rank] >= 1
        && [Rank] < 5,
    "0-5",
    IF (
        [Rank] >= 5
            && [Rank] < 15,
        "5-15",
        IF (
            [Rank] >= 15
                && [Rank] < 30,
            "15-30",
            IF ( [Rank] >= 30 && [Rank] < 45, "30-45" )
        )
    )
)

Here is my table output:

 

1.7.3.1.PNG

 

Then use the following formula to calculate the cumulative percentage:

Measure =
CALCULATE (
    SUM ( 'Table'[Percentage] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )
)

In addition, in order for the x-axis to be displayed in the correct order, put the Rank to Tooltip pane.

 

The final output is shown below:

1.7.3.2.PNG

Please kindly take a look at the pbix file.

 

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

 

View solution in original post

Super User IV
Super User IV

@Beginner_2020 , if day is a column then you can create a static bucket. If measure then you need to do dynamic Segmentation

 

For % of Cumm total, you have try a measure

divide(calculate([Gt count], filter(allselected(Table), Table[days] <= max(Table[days] ))), calculate([Gt count], allselected(Table)))

 

segmentation

https://www.youtube.com/watch?v=CuczXPj0N-k

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors