Percent of Total/ % of cumulative Total Day by Day

[Neffri]

Can someone please help with figuring out how to create % of cumulative total for the below data? I'd like to have it broken down by region and country. First day's Total Amount is an amount to be paid and I need to count daily the percentage of the total that was already paid (Amount from day2 - amount from day1, etc.)

As mentioned - below you can find a sample data.

 

Thank you for your advices.

[DavidSkaife]

Hi @Neffri 

Here is a way of doing it;

Assign a unique RecordID to each row, remove the first instance of each Region and Country, add join it back on to the remaining as a new column. Perform the calculation using the below formula

ToString(Round((([Right_Total Amount]-[Total Amount])/[Right_Total Amount])*100,1))+'%'

Union this stream and the original first instances of each Region/Country combo, remove any unwanted fields, sort by RecordID

[jrgo · Accepted solution]

Like this?

[Neffri]

Thank you very much it's working :)

I have additional question. I need to present % of change for country ( which you helped me with) but also for Region.

So depending what user will select in the report it needs to switch from Region % to Country %.

So for Region it should be (for Europe):

 

Is there a way to do both % in one column, in order for it to work for country and for Region split?

Do I need to change the way of presenting data?

Can you please guide me what is the correct approach ?

Thank you !

[DavidSkaife · Accepted solution]

Hi @Neffri 

I've updated my workflow to include a calculation for Region as well, which i then join back to the main (which puts the data against each region)

Personally i wouldn't put it in the same column as that may not look right, but it's up to you how you want to present the data. My workflow should give you the guide on how to calculate it and then you can go from there :)

[Neffri]

@DavidSkaife thank you, if I understand correctly each level of granularity need to have own % of total, correct?

[DavidSkaife]

I would say so yes, it would present better

[Neffri]

Hi @DavidSkaife 

I was able to create % of Total for Region, Country, and even for Manager it works, but I have problem with the another level which has duplicates.

For "Team" skipping doesn't work. Is it possible to do % of total for team? Can you please advice me?

Date	REGION	Country	Team	Total Amount
01/01/2023	Europe	Germany	T1	1000
01/01/2023	Europe	France	T1	2000
01/01/2023	Europe	Italy	T2	3000
01/01/2023	Europe	Spain	T3	1500
01/01/2023	Europe	Spain	T4	2500
01/01/2023	Europe	Greece	T5	5000
02/01/2023	Europe	Germany	T1	900
02/01/2023	Europe	France	T1	1500
02/01/2023	Europe	Italy	T2	4000
02/01/2023	Europe	Spain	T3	1800
02/01/2023	Europe	Spain	T4	400
02/01/2023	Europe	Greece	T5	4100

[DavidSkaife]

Hi @Neffri 

Can you share how you would expect the output to look?