cancel
Showing results for
Did you mean:
Frequent Visitor

## Subscription revenue based on transaction-level data

Hello all,

I have some transaction data that only gets entered where there is activity from the client, with commission earned by me. I need to calculate the current commission based on this data. To illustrate the solution needed, here is some dummy data:

 Customer Age Transaction Type Product Price Transaction Date A 20 Purchased 1 30 01-Oct-22 B 40 Purchased 2 50 31-Oct-22 A 20 Cancelled 1 30 31-Oct-22 C 60 Purchased 1 10 01-Nov-22

Just to calculate the commission, I will need to multiply the price by a certain percentage based on the product table:

 Product Commission 1 1.00% 2 0.50%

And the result I get should be as follows (comment to illustrate what is going on):

 Month Commission earned Comment Oct-22 0.55 Customer A & B Nov-22 0.35 Customer B & C

So my question here is:
1. How do I get the brokerage earned in October to show up in November (and other future months/quarters/years) as well? To incorporate the use of different levels of granularity splicing (i.e. months, quarters, years).

2. How can I take into account the second customer A transaction where he cancelled it and does not show up in November? An idea I have is to include a commission flag column where the "purchased" show up as 1, and "cancelled" show up as -1 so it will cancel out in the future months (linked to question 1 above) when multiplied with the price. Does this make sense? And how could I implement it?

It would be good if this could all be geared towards Power Pivot - I understand Power Pivot and Power BI can be slightly different.

PS: There's another layer of complexity to this that I will post as a separate question if you could help with as well.

6 REPLIES 6
Community Support

Hi @User068765 ,

``````Measure =
var _a = ADDCOLUMNS(ALL('Table'),"commission",[Price]*IF([Transaction Type]="Cancelled",-1,1)*CALCULATE(MAX('Table (2)'[Commission])))
return SUMX(FILTER(_a,[Transaction Date]<=MAX('Date'[Date])&&[Subscription End Date]>MAX('Date'[Date])),[commission])``````

Refer to:

How to provide sample data in the Power BI Forum

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

Hi @User068765 ,

``````Measure =
var _a = ADDCOLUMNS(ALL('Table'),"commission",[Price]*IF([Transaction Type]="Cancelled",-1,1)*CALCULATE(MAX('Table (2)'[Commission])))
return SUMX(FILTER(_a,[Transaction Date]<=MAX('Date'[Date])),[commission])``````

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Thank you very much for this - it seems to be getting close to what I am trying to achieve. I have been trying to modify it to account for the fact that there is also expiry date for these transactions but don't seem to be getting anywhere.

 Customer Age Transaction Type Product Price Transaction Date Subscription End Date A 20 Purchased 1 30 01-Oct-22 01-Oct-23 B 40 Purchased 2 50 15-Oct-22 15-Oct-25 A 20 Cancelled 1 30 08-Oct-22 01-Oct-23 C 60 Purchased 1 10 01-Nov-22 01-Nov-24 C 60 Cancelled 1 10 01-Jan-23 01-Nov-24

I tried modifying the Date in the SUMX but that doesn't seem to work. Basically, after the end date for each customer, I should not be earning commission anymore. If we were looking at it from the point of 2021, I should also not be earning anything from 2021 to October 2022 since the first transaction start then. Is there any way to incorporate this?

Community Support

Hi @User068765 ,

Please explain in detail why there is no Customer A's commission in the November commission? Is the calculation of commission based on the comments? Or is there some other calculation logic?

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario? Please provide me with more details about your table and your problem.

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Apologies for the lack of clarity. Customer A will not be considered in November because  he cancelled on 31-October-2022. You have caught the crucial point to the difficulty I am facing - I am assuming that all entries will keep earning commission for the month unless I am told otherwise. How do I take into account the fact that I will not be including commission for customer A in the later months? One thing I can think of is to have a sort of flag where purchased will be 1, and cancelled will be -1, which will cancel the subsequent months.

I realise the dates may be causing confusion, so let me add a couple of other scenarios and amend the above scenario.

 Customer Age Transaction Type Product Price Transaction Date Oct-22 Nov-22 Dec-22 Jan-23 Comments A 20 Purchased 1 30 01-Oct-22 = 0.01 * 30 None earned for October as customer cancelled in subsequent transaction B 40 Purchased 2 50 15-Oct-22 = 0.005 * 50 = 0.005 * 50 = 0.005 * 50 = 0.005 * 50 Earned until current date (whenever this is) as no subsequent cancellation A 20 Cancelled 1 30 08-Oct-22 =- 0.01 * 30 See Customer A above C 60 Purchased 1 10 01-Nov-22 = 0.01 * 10 = 0.01 * 10 = 0.01 * 10 Earned from Nov to Dec, but Jan cancelled by subsequent transaction C 60 Cancelled 1 10 01-Jan-23 =- 0.01 * 10 See above

The way I can think of of taking into account the later entries of data is by adding a flag of sorts (+1/-1) so that the items cancel out like below. But I am not sure how to implement this by calculating commission for subsequent months based on one line entry? Do I use a cross table?

 Customer Age Transaction Type Product Price Transaction Date Oct-22 Nov-22 Dec-22 Jan-23 A 20 Purchased 1 30 01-Oct-22 = 0.01 * 30 = 0.01 * 30 = 0.01 * 30 = 0.01 * 30 B 40 Purchased 2 50 15-Oct-22 = 0.005 * 50 = 0.005 * 50 = 0.005 * 50 = 0.005 * 50 A 20 Cancelled 1 30 08-Oct-22 =- 0.01 * 30 =- 0.01 * 30 =- 0.01 * 30 =- 0.01 * 30 C 60 Purchased 1 10 01-Nov-22 = 0.01 * 10 = 0.01 * 10 = 0.01 * 10 C 60 Cancelled 1 10 01-Jan-23 =- 0.01 * 10

Final commission earned should be as follows:

 Oct-22 Nov-22 Dec-22 Jan-23 0.25 0.35 0.35 0.25

Thank you very much for your help! And do let me know if there's anything I can do to make it clearer.

Frequent Visitor

Hello @v-jianboli-msft

I have gotten to a place where it is quite close to what I would like to achieve based on mede's answer in this thread. However, I faced a similar issue as the original poster where the subtotal does not seem to show a sum for the year but is instead the exact figure calculated. I understand that the logic is probably not exactly correct hence this issue. Is there a way to achieve a similar result but with the correct subtotal?

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors