0

I have a list of product with revenues across 3 years. However I would like to split the revenue by 60 % and 40% for each year.

For instance:

  • Book -> 2020 -> 15mil
  • Book -> 2021 -> 18mil
  • Book -> 2022 -> 12mil

Therefore in Power BI the revenue should appear as:

  • 2020 -> 60% x 15
  • 2021 -> 40% x 15 + 60%*18
  • 2022 -> 40% x 18 + 60% x 12
  • 2023 -> 40% x 12

How can I code this in Power BI to ensure that the revenue are split accordingly?

Update:

I have tried to do a 60% and 40% split column and additional column of Year +1. How do I add them up on Power BI based on year?

bad_coder
  • 11,289
  • 20
  • 44
  • 72
milkywaypowerbi
  • 1
  • 1

1 Answers1

0

I assumed some data model like the following: PowerBi datamodel

[SalesAmt] = SUM(paid_price)

If we now create a table out of the year and [SalesAmt] we get the following. (My sales values were created for testing purposes)

data table v1

Now for my understanding: You want to display the SalesAmt (which exact Measure doesn't matter) multiplied by 60% and add the SalesAmt from the previous year multiplied by 40%.

For this we can use the DAX formula SAMEPERIODLASTYEAR(), it takes one argument, the date-field for which you want to retrieve the last year's pendant.

[SalesAmt Buckets v1] = 
    SALES[SalesAmt] * 0.6
    + CALCULATE(SALES[SalesAmt], SAMEPERIODLASTYEAR(DATES[fq_date])) * 0.4

Explanation to this formula:

We calculate the current year's [SalesAmt] and multiply it by 60%, then we calculate [SalesAmt] and changing the filter context for this calculation by CALCULATE(). At the end we multiply by your 40% and return the result.

data table v2

Notice how it is less for the firs year (2019), that's because we don't have a previous year for the row context year 2019, so we just retrieve the current year's value times 40%. For the next years it's correct being 2020 = 40% * 79 + 60% * 95 = 85.

KaiE
  • 16
  • 2
  • for a better answer you could provide the structure of your tables. I assume you're using a dimension table for your date and a fact table for your sales. Otherwise you need to change stuff around, let me know if you need more help. – KaiE Sep 02 '21 at 11:39
  • How can I attach my pbix file? Also, if I would like to conduct more than 2 spilt, for instance (20%/30%/40%/10%) will this formula still work? Thanks – milkywaypowerbi Sep 03 '21 at 04:28
  • To attach a file see: https://meta.stackexchange.com/questions/47689/how-can-i-attach-a-file-to-a-post – KaiE Sep 09 '21 at 05:17