cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
great_AS Member
Member

cumulative percentages

Hi!

 

I need to calculate the cumulative percentage as in the file attached.

 

What I need is to have another column with cumulative percentage with 25%, 62,5%, 87.5%, 100%.

 

Any idea how to do it? I checked this thread but couldn't make it.

 

Thanks

 

Cheers,

Andrea

2 ACCEPTED SOLUTIONS

Accepted Solutions
Anonymous
Not applicable

Re: cumulative percentages

Hi great_AS,

See this image and tell us if works for you.

 

test.JPG

View solution in original post

v-danhe-msft Super Contributor
Super Contributor

Re: cumulative percentages

Hi @great_AS,

You could refer to Miltinho's solution, see below code:

Measure = 
var sales=CALCULATE(SUM('TEST'[Sales]),FILTER(ALLSELECTED(TEST[Collection]),ISONORAFTER('TEST'[Collection],MAX('TEST'[Collection]),DESC)))
var totalsales=CALCULATE(SUM('TEST'[Sales]),ALLSELECTED(TEST))
return DIVIDE(sales,totalsales)

Result:

1.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

 

Community Support Team _ Daniel He
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

7 REPLIES 7
Anonymous
Not applicable

Re: cumulative percentages

Hi great_AS,

See this image and tell us if works for you.

 

test.JPG

View solution in original post

great_AS Member
Member

Re: cumulative percentages

HI @Anonymous!

 

That's perfect!! Can you please share your formula here so I can copy and paste it?

 

Thanks.

 

Cheers,

Andrea

 

Anonymous
Not applicable

Re: cumulative percentages

Andrea, I solved it then I deleted it.

Super User
Super User

Re: cumulative percentages

Hi,

 

To compute the cumulative %, how does one know the order to be followed.  Looking at your data, the order is definitely not the Sales.  Is the order the Collection column - Autumn, Spring, Summer and then Winter?

 

Please clarify.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
great_AS Member
Member

Re: cumulative percentages

HI @Ashish_Mathur, exactly, the order is the collection.

Super User
Super User

Re: cumulative percentages

Hi,

 

Here's what i did.  I first wrote this calculated column formula to get the indexing by season

 

Order of seasons = if([Collection]="Autumn",1,if([Collection]="Spring",2,if([Collection]="Summer",3,4)))

 

I then wrote these measures

 

Revenue = SUM(TEST[Sales])
Season order = if(HASONEVALUE(TEST[Collection]),SUM(TEST[Order of seasons]),BLANK())
Cumulative revenue = SUMX(TOPN([Season order],CALCULATETABLE(VALUES(TEST[Collection]),ALL(TEST[Collection])),[Season order],ASC),[Revenue])
Total revenue = CALCULATE([Revenue],ALL(TEST[Collection]))
Cumulative revenue/Total revenue = [Cumulative revenue]/[Total revenue]
 
In the visual. i dragged Revenue and Cumulative revenue/Total revenue.
 
Hope this helps.
 
Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-danhe-msft Super Contributor
Super Contributor

Re: cumulative percentages

Hi @great_AS,

You could refer to Miltinho's solution, see below code:

Measure = 
var sales=CALCULATE(SUM('TEST'[Sales]),FILTER(ALLSELECTED(TEST[Collection]),ISONORAFTER('TEST'[Collection],MAX('TEST'[Collection]),DESC)))
var totalsales=CALCULATE(SUM('TEST'[Sales]),ALLSELECTED(TEST))
return DIVIDE(sales,totalsales)

Result:

1.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

 

Community Support Team _ Daniel He
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

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 355 members 2,862 guests
Please welcome our newest community members: