Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

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
HotChilli
Super User
Super User

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.