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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
fjmontes
Frequent Visitor

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

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.  

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

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

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

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

Best
D.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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