cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Soly_Az
Frequent Visitor

How to dynamically show category members as % of the selected category on x-axis of a Pareto chart

I have created a Pareto chart to show the Cumulative % of Spend for a series of vendors according to the video below:
https://www.youtube.com/watch?v=rlUBO5qoKow

 

which works fine:

Soly_Az_0-1626270452255.png

 

However, as the users previously used to create the chart in Excel, they would like to have "cumulative % of vendors" in the x-axis instead of the vendors; such as the following chart (which is made manually only to illustrate the desired result). In this way the users can interpret the data easier, i.e. "33% of the vendors account for 77% of the total spend".

Soly_Az_1-1626270560656.png

 

Any idea if this is possible to do?

Thanks in advance for any suggestions!

2 REPLIES 2
V-pazhen-msft
Community Support
Community Support

@Soly_Az 

 

Basically, you need to create a column to get the cumlative percent of total venders. 

1. Add an index column.

2.  Create a calcuate column 

Column = CALCULATE(COUNTROWS('Table'),FILTER('Table',[Index]<=EARLIER('Table'[Index])))/ COUNTROWS('Table')

 

Please check my example:

Vpazhenmsft_0-1626417180522.png

 

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

 

 

Thanks! However, the use of Earlier and the fact that my vendor-table has 84000+ rows, the formula causes performance issues and won't work.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

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