cumulative percentages

[Anonymous]

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

[Anonymous · Accepted solution]

Hi great_AS,

See this image and tell us if works for you.

[Maldarraji]

Hi All can any one help to calculate cumulative for (allocation_rat) column ?

[Ashish_Mathur]

Hi,

You should have a Calendar Table with a relationship (Many to One and Single) from the Allocation_date column of the Data Table to the Date column of the Calendar Table.  From your visual, remove the Allocation_date column and drag the Date column from the Calendar Table.  Assuming allocated_rate is a measure, write this measure and drag it it your visual

YTD allocate_rate = calculate([allocated_rate],datesytd(calendar[date],"31/12"))

The YTD calculation will restart on January 1 of every year.

Hope this helps.

[Maldarraji]

Hi Mr. Ashish, I'm a new intern at The PowerBI, and I don't know how to get the Cumulative when I worked the Calender they give me error. could you please help to create table have cumulative rate starting from 01/01/2015  to 30/12/2021  

[Maldarraji]

any equation that can be applied to get such a result in the yellow color?

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Maldarraji]

Sorry but the PBI file in my laptop.Do you want me send you the file

[Ashish_Mathur]

Upload the file to Google Drive and share the download link of the file here.

[Maldarraji]

Mr. Ashish

this is my link file

https://drive.google.com/file/d/1eszmxKNjvi1yfqTkZG2ky3PyIFyYtEOZ/view?usp=sharing

[Ashish_Mathur]

Access Denied message.

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

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:

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

Regards,

Daniel He

[AliceW]

Can confirm it still works and it's still needed! 🙂 Thanks, man!

[Ashish_Mathur]

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.

[Anonymous]

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

[Ashish_Mathur]

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.

[Anonymous · Accepted solution]

Hi great_AS,

See this image and tell us if works for you.

[Anonymous]

HI @Anonymous!

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

Thanks.

Cheers,

Andrea

[Anonymous]

Andrea, I solved it then I deleted it.