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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jh292
Helper I
Helper I

Cumulative totalling

Hi team,

 

I have a table imported from a folder with monthly reports for all customer and all their products, and a simple messure to total monthly revenue (sum monthly revenue, nothing fancy!) this is a large report with neumorous customers and their allocated Account Manager

 

I need to replicate the below calculations and then apply some calculations for a bonus;

Monthly RevMar-20Apr-20May-20Jun-20
Rev Total£40,000£40,500£40,750£41,000
Cumualtive Target £40,000£80,000£120,000
Cumualtive Actual £40,500£81,250£122,250
+/- Target % 101%102%102%

 

If "Target" is positive (above 100%), pay sales staff a set figure - i.e. they have a bonus for keeping revenue up in their accounts. they get £0 if it is below 100%

 

Therefore I have a seperate table with the bonus figures;

 

Account ManagerRetention Bonus
John Smith£8,000
Joe Bloggs£5,000
Suzzie Smith£12,500

 

I dont need to use the %'s possibly just the cumulative figures it doesnt matter. 

 

The revenue table is refreshed monthly but bonus is paid quarterly. Therefore the messure might need to be fixed, which is fine. I.e. I need to be showing sales people whether they are on track each month of a quarter. 

 

so, calculate cumulative total, compare to target, if greater for sales persons account return Rentention Bonus, else £0. 

 

Hopefully I have explained that well. 

 

Thanks in advance 

James

 

 

1 REPLY 1
V-pazhen-msft
Community Support
Community Support

@jh292 

Please share a sample pbix, text description is not clear for us to understand the model.

 

Read this post to describe your question:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Regards
Paul

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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