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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors