cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vecksed New Member
New Member

How do I make a cumulative % line with below data?

 

Hi All,

 

I could use some help with this. I'm trying to come up with a chart as below. The x-axis will be the "Day-Bucket", The primary vertical axis will be Qty and the secondary vertical axis will be the cumulative % of the quantity for the month-year.

https://www.tulane.edu/~salem/Images/Cumulative-Histogram.gif

 

Sample data is as below, I wish to use the line and clustered column chart but I'm not sure on how to generate the column for the cumulative % line in DAX. The cumulative % line should adjust based on the filtered Month-Year. Any help is appreciated, thank you.

Day BucketQtyMonth-year
0-275410-2018
>30110-2018
0-266711-2018
3-5111-2018
6-8411-2018
12-14511-2018
9-11111-2018
1 REPLY 1
Super User I
Super User I

Re: How do I make a cumulative % line with below data?

There are quite a few aspects to the challenge.

First, you'll need to define an order on the buckets.  This will allow you to display the visual correctly AND calculate the cumulative % correctly.

One way to do this is to extract the distinct bins to a table.  You could construct the table manually (in excel) or experiment in Power Query but what you want is a 2 column table with

Bin   Index

0-2     1

3-5     2

and so on

 

You then create a relationship between the bins(buckets) on both tables.

 

Next, get a slicer for Month-year.

Then make sure that you can show Qty on a visual with buckets in the correct order.  Use 'Sort by Column' feature to sort buckets by the Index.

 

If you do this succesfully, write a measure to work out the cumulative quantity.  Use a table visualisation to test this.

From there, use the measure you've just written to construct a % change measure.

Then put it all together on the Line and Clustered column chart.

If i was you i'd change the test data to something which shows the changes more distinctly because 1,4,5 are very small compared to 667 so the chart will have a large initial value then flatline.

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors