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
Anonymous
Not applicable

DAX formula to calculate Allocated Revenue based on last year total revenue

I want to create a DAX formula as follows 

 

I have 4 columns Deal#, Unit, Year, Proposed Budget.  I want to allocate revenue based on the % increase between last year total proposed budget and this year propose budget.

 

Say 

2017 Proposed Budget was $115,000 and

2018 Proposed Budget was $125,000

 

% increase is 8.70%

(125000-115000)/115000

 

Now, for 2018 I have to allocate revenue as follows

 

(1 + 0.0870) * 2018 Proposed Budget 

1.0870 * Each unit proposed budget

 

I tried this but it did not work.  I have many deals 

 

Created four measures

 

1)

  TotalProposedBudget = SUM(Contract[ProposedBudget])

 

2) 

TotalProposedBudgetLY= CALCULATE(Contract[TotalProposedBudget],SAMEPERIODLASTYEAR(Contract[contractenddate].[Date]))
 
3)
Delta TY & LY = [TotalProposedBudget ] - [TotalProposedBudgetLY]
 
4)
% Delta = [Delta TY & LY] / [TotalProposedBudget ]
 
The calculation is done at the row level.  It is not an aggregate of the Total for the year.  Please help

 

Annotation 2019-08-22 093858.png

13 REPLIES 13
Anonymous
Not applicable

It looks like you need a calculated column in your table, Allocated Revenue. So, a question is: Why don't you use Power Query?

Unless I miss something...

Best
Darek
Anonymous
Not applicable

I tried using a calculated column but it sum the entire table I need it to sum only by deal#, and last year.  I am using Power BI for a visualization.  

Anonymous
Not applicable

I am new at DAX and I am having difficulties unterstanding the use of filters at the row level.  How can I define a filter at the row level.

 

 

Anonymous
Not applicable

You should forget DAX and jump into Power Query. This is the best advice I can give you. DAX is not a tool to do this kind of stuff unless... you want to have nightmares.

Best
Darek
Anonymous
Not applicable

Thanks for your advise.  But how would I do that in power query.  I will try it.

Anonymous
Not applicable

I'll do that for you but you have to attach a file where you have some example data.

Best
Darek
Anonymous
Not applicable

https://app.powerbi.com/groups/51b8fe5d-7153-43e5-b0bd-82d23f638902/reports/29a5ec84-5356-4def-8064-...

 

I am trying to upload the report but I cannot do it.  here is the link to the report how can I share it with you.

Anonymous
Not applicable

By the way... I cannot access the report via the link.

Best
D.
Anonymous
Not applicable

Anonymous
Not applicable

Will get down to it when I'm back home in about an hour...

Best
D.
Anonymous
Not applicable

Thank you!

Anonymous
Not applicable

You can paste a link to the file if you put it in OneDrive or Google Drive, or Dropbox. Don't forget to set the permissions correctly, so that I can access it.

Best
D.
Anonymous
Not applicable

I said POWER QUERY. Not DAX.

Best
Darek

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.