Forum Discussion

BIanon's avatar
BIanon
Helper V
3 years ago

Calculating incremental contribution

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.

Need Help

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      BIanon
      Helper V
      3 years ago

      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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      BIanon
      Helper V
      3 years ago

      Thank you once again.

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        HI BIanon ,

        yes try with calculated column

    • BIanon's avatar
      BIanon
      Helper V
      3 years ago

      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 ?