cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
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]
)```

Resolver I

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 ?

Resolver I

Thank you once again.

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

Responsive Resident

HI @BIanon ,

yes try with calculated column

Resolver I

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

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

Resolver I

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors