Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
BIanon
Resolver I
Resolver I

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.

7 REPLIES 7
vs_7
Responsive Resident
Responsive Resident

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!!!

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 ?

Thank you once again.

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

vs_7
Responsive Resident
Responsive Resident

HI @BIanon ,

yes try with calculated column

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
Responsive Resident
Responsive Resident

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

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 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.