Calculating incremental contribution

[BIanon]

Hello Community,

I have a case which to me is pretty tricky to get started on. I will explain the scenario and then finish off with some of my own thoughts on how to structure this.

Scenario:

Given a user's slicer selections generate a list of Products, sorted by sales amount, then calculate 'unique contribution' to sales. So when calculating sales amount for product #2 I have to exclude the customers that bought product #1, for product #3 I would exclude customers who bought #1 and #2.

I can statically do this by calculating a table with users who bought #1 and use that as a filter for #2 but doing it dynamically so the measure will recognize all relevant products and 'cycle' through them has me a bit stumped.

Essentially, how do I rank a list of products then cycle through them and calculate the sales amount excluding the previous products customers.

I hope I have explained myself with relative clarity otherwise feel free to ask for more info.

[vs_7]

hi @BIanon ,

check below measures

Product Rank = RANKX(ALL('YourTable'[ProductID]), SUM('YourTable'[SalesAmount]), , DESC, Skip)

or 

Cumulative Sales = 
SUMX(
   FILTER(
      ALL('YourTable'),
      'YourTable'[Product Rank] <= EARLIER('YourTable'[Product Rank])
   ),
   'YourTable'[SalesAmount]
)

Please Accept as Solution If resolved your problem!!!

[BIanon]

Hello again,

I have tried implementing your pattern but doing the first measure as a calculated column completely crashed my client so I had to add it to the table in my DW.

Now I'm trying to implement your second measure but it suddenly hit me; doing static ranking like this doesn't support filtering on time, does it ?
Filtering on something like category is fine because a product can only belong to one category but when my users start changing time, the value and thus ranking change?

There HAS to be a way to work with rankings in PBI that is dynamic ?

[BIanon]

Thank you once again.

The [Product Rank] measure, would that have to be a calculated column?

[vs_7]

HI @BIanon ,

yes try with calculated column

[BIanon]

Hello again,

I tried adding the ranking to my dimension via my DW as the calc. col was too much to run on my model.

The problem is, doing it this way removes the ability to slice on time since the value i am ranking on needs to change as the user moves the time slicer.

I can rank dynamically, but I can't find a way to then work with those rankings since EARLIER() only accepts a col ref

Do you have an idea for this?

Also, thank you so far for your time and knowledge

[vs_7]

HI  ,try below steps:

1. Create a measure to calculate the cumulative sales amount for each product:
   DAX

   Cumulative Sales = CALCULATE(SUM(Sales[Amount]), FILTER(ALLSELECTED(Products), Products[ProductID] <= MAX(Products[ProductID])))

   This measure calculates the cumulative sales amount by summing the sales amount for all products up to the current product based on their ProductID.

   1. Create a measure to calculate the unique contribution to sales for each product:
      DAX

      Unique Contribution = [Cumulative Sales] - CALCULATE(SUM(Sales[Amount]), FILTER(ALLSELECTED(Products), Products[ProductID] < MAX(Products[ProductID])))

      This measure subtracts the sales amount of the previous products from the cumulative sales amount to calculate the unique contribution.

@BIanon

[BIanon]

Hello and thank you for your reply.

I am currently testing out your method but am experincing that the sum is cumulative across the alphabetical order of my ProductID's (These are unfortunately text strings).

I need the cumulative sum to start from the product with the highest sales amount and then move down in rank. Is this achieveable ?

will also continue to see if i can solve it myself