cancel
Showing results 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

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

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors