topic Re: DAX formula to calculate Allocated Revenue based on last year total revenue in DAX Commands and Tips

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

Anonymous — Thu, 22 Aug 2019 16:49:11 GMT

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

TotalProposedBudget = SUM(Contract[ProposedBudget])

TotalProposedBudgetLY= CALCULATE(Contract[TotalProposedBudget],SAMEPERIODLASTYEAR(Contract[contractenddate].[Date]))

Delta TY & LY = [TotalProposedBudget ] - [TotalProposedBudgetLY]

% 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

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

Anonymous — Thu, 22 Aug 2019 17:10:45 GMT

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

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

Anonymous — Thu, 22 Aug 2019 17:14:27 GMT

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.

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

Anonymous — Thu, 22 Aug 2019 17:21:28 GMT

I said POWER QUERY. Not DAX.

Best
Darek

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

Anonymous — Thu, 22 Aug 2019 17:21:46 GMT

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.

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

Anonymous — Thu, 22 Aug 2019 17:23:39 GMT

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

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

Anonymous — Thu, 22 Aug 2019 17:26:16 GMT

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

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

Anonymous — Thu, 22 Aug 2019 17:27:31 GMT

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

Best
Darek

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

Anonymous — Thu, 22 Aug 2019 17:46:00 GMT

https://app.powerbi.com/groups/51b8fe5d-7153-43e5-b0bd-82d23f638902/reports/29a5ec84-5356-4def-8064-8ab4592378da?ctid=f02b3a29-4c40-43d7-8e3b-ccebb6e2e557

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.

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

Anonymous — Thu, 22 Aug 2019 17:49:29 GMT

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.

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

Anonymous — Thu, 22 Aug 2019 17:51:16 GMT

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

Best
D.

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

Anonymous — Thu, 22 Aug 2019 18:15:18 GMT

https://drive.google.com/file/d/1LoLGTkkvuoigte0iaq-1Y1RrnTXXESIn/view?usp=sharing

Here is the file.

Thanks for all your help.

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

Anonymous — Thu, 22 Aug 2019 18:21:10 GMT

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

Best
D.

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

Anonymous — Thu, 22 Aug 2019 18:27:49 GMT

Thank you!