Calcularte revenue sharing in a many to many context?

[nironixon]

Hi there, Im all new to Power Bi and DAX and cant really figure out how to calculate
revenue sharing in a many to many scenario.

I have the following "domain" model:

1. Each Orderline references one "Article".
2. Each Article has one or more "Channels" associated.
3. Each combination of an Article and a Channel has an "Allocation key".
   The "Allocation key" dictates the revenue share that should be paid to the "Channel".

Im now supposed to create a report that answers the following in a pivot report:

For each date what is the revenue for each channel?

"Pseudo code":

1. for each date:
   1. for each channel:
      sum += Orderline.Amount * Article.Bridge_Article_Channel.Allocation Key

Do you have any idea how to solve this? 
Any help is very appreciated!

Regards
Niclas

[MFelix]

Hi @nironixon,

Not sure how your data should look like in terms of allocation but made a sample based on your image.

Create a measure with the following code:

Price allocated =
CALCULATE (
    SUMX (
        OrderLines;
        OrderLines[Price] * RELATED ( Bridge_Article_Channel[AllocationFormula] )
    )
)

As you can see below on my sample this is calculating the values of the price multiplied by the allocation, since it's a measure is based on context so if you change your visualsfor date/ channel should give what you need.

Regards,

MFelix

[nironixon]

Hi @MFelix, many thanks for all your efforts trying to help me, really appreciate it!

I tried to implement your solution but it doesn't work. I guess we are using a different model and relationships.
When creating the Measure "Price allocated", i cant reference the bridge table "Bridge_Article_Channel".

I have attached a pbix file with my model and some test data so we have the same view of the problem.

https://mskpistormaster.blob.core.windows.net/share/calle.pbix

Once again, appreciate your efforts!

Regards
Niclas

[MFelix]

Hi @nironixon,

In my table I only add one to one relation between bridge and article, how do you know in the order lines to wich channel your product relates to?

If you add the article tpo the table you have placed on the PBI you get multiple lines per article wich is not true since you only sold 100 of each.

The result is giving you the final 300 since you have the cross filter between article and Bridge to both however this is not the correct result since you get 3 lines each with 100 for product 3.

Regards,

MFelix

[nironixon]

Hi again @MFelix!!

Would it be possible for you to share a pbix file of your solution or a screendump of your model and relations,
i can't set a one to one relationship between Articles and Bridge_Articel_Channel table. I get an error message saying "The cardianlity you selected isnt valid for this relationsship". 

The error message makse sense to me, the tables are a classic many to many setup so how can we have a one to one relation?

Really appreciating your time!

Best regards 

Niclas

[MFelix]

Hi,

I considered that a product would only had a channel.

That's why it worked. How do you related the 100 for product 3 to each one of the channels?

[nironixon]

@MFelix, 

I suspected that you had the case wrong, thats why I took screendumps and also share my pbix file. 

1. You write about "products", I assume you are refering to what i have called Articles, correct?
2. I dont understand your querstion about how i relate the 100 product to 3?
   If you look on my tables and their realationships it should be clear how i relate things, no?

Regards Niclas

[MFelix]

Hi @nironixon,

1. Yes products = article 😄

2. The relationship are perfectly understand, what I don't get is how you make the allocation between the price and the  Allocation formula for each channel.

You have Article3 that as 1 order line with 100, however you have 3 channel connected to Article3 with 3 allocation formula.

Do you want to have article3 with 100*0,1 + 100*0,2 + 100*0,3 or only by one of the channels?

Regards

MFelix

[nironixon]

Hi again @MFelix 

Ok, lets see if I can describe my problem like this.

One "Orderline" is placed for a given "Article" with a certain "Amount".
One "Article" is referenced by one to many "Channels" that has an associated "Allocation Formula" used when
calculating the revenue share that should be paid to the "Channnel".

Lets take Orderline with Id = 2 as an example:

1. Has an Amount of 100.
2. Orderline references Article 2.
3. Article 2 is referenced by:
   1. Channel 1 with an Allocation Formula of 0,1
   2. Channel 2 with an Allocation Formula of 0,2

Now, to calculate the amount that should be paid to the owner of the "Chanel", the revenue sharing,

Channel 1: 100 * 0,1 = 10
Channel 2: 100 * 0,2 = 20

The revenue sharing should be presented in a matrix report with

• Channels as Rows
• Dates as Columns
• Revenu sharing as Values

So, Revenue sharing is the amount that should be paid to each Channel owner calcluted
as Amount * AllocationFormula

Best Regards Niclas

[nironixon · Accepted solution]

HI @MFelix and others, 

I think I have solved my problem and would like to share it with you since you were kind trying to help me out!

1. I restructured the underlying data model and relations but have keept the required many to many relation.

2. A new table called "Allocations" that stores the ArticleId, Channel and Allocation formula.

To calculate the revenu sharing for each channel i create two measures. I use DAX function LOOKUPVALUE to find the current allocation formula neded when calculating the revenue sharing. 

Seems to be working, giving correct result on my test data. Not sure though if thera are hidden bugs in this that i haven realized yet. Learning DAX is bloody !

RevenueSharing =
CALCULATE (
    SUMX (
        OrderLines;
        OrderLines[Amount] * [AllocationKey]
    )
)

AllocationKey =
LOOKUPVALUE (
    Allocations[Allocation];
    Allocations[ArticleId]; SELECTEDVALUE ( OrderLines[ArticleId] );
    Allocations[Channel]; SELECTEDVALUE ( Allocations[Channel] )
)

I have also updated the pbix file if you want to have a look

https://mskpistormaster.blob.core.windows.net/share/calle.pbix

Regards Niclas

[MFelix]

Hi @nironixon,

Glad that you found the solution and sorry for the late response but had some work priorities to attend.

Regards,

MFelix

[v-yuezhe-msft]

@nironixon,

Glad to hear the issue is solved, you can accept appropriate replies as answers, this way, other community members could easily find the answer when they get same issues.

Regards,
Lydia