cancel
Showing results for
Did you mean:

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

Helper I

## DAX HELP - Back filtering part table based on percentage of total sales

I am looking for some help "back filtering" sales.

The requirement to to retunr the Top X products that contibute to 80% of the total sales value.  I am guessing that I could use a combination of a cumulative total and rankx somehow.

I have a the following tables in my model,

FactSales

DimDate

DimProduct

DimCustomer

Any help greatfully received

1 ACCEPTED SOLUTION
Community Support

Hi @NanDeb ，

Please create following measure:

``````cumulative_sales(80%) =
var cumulative_sales = CALCULATE(SUM('Table'[VALUE]),FILTER(ALL('Table'),'Table'[TaxDate] <= SELECTEDVALUE('Table'[TaxDate])))
var total_sales = CALCULATE(SUM('Table'[VALUE]),ALL('Table'))
return
IF(cumulative_sales <= total_sales * 0.8, cumulative_sales)``````

I think this is the result you want:

Best regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4
Helper I

to start with I would like returned a table of all the products and their cumulative sales.

If possible I would then also like to exclude the lower ranking sales after cumulative value of 80% of total sales value is reached

EG,

Total Sale Value = £800,000
80% Sales Value = £640,000

I want to return something like this:

 Product ID Rank Total Sale Value Cumulative AAA 1 £90,000 £80,000 BBB 2 £85,000 £175,000 CCC 3 £80,000 £255,000 DDD 4 £75,000 £330,000 EEE 5 £70,000 £400,000 FFF 6 £65,000 £465,000 GGG 7 £60,000 £525,000 HHH 8 £55,000 £580,000 III 9 £50,000 £620,000 JJJ 10 £45,000 NOT INCLUDED If possible

Is there anyway of achieving this in DAX

Community Support

Hi @NanDeb ，

Please create following measure:

``````cumulative_sales(80%) =
var cumulative_sales = CALCULATE(SUM('Table'[VALUE]),FILTER(ALL('Table'),'Table'[TaxDate] <= SELECTEDVALUE('Table'[TaxDate])))
var total_sales = CALCULATE(SUM('Table'[VALUE]),ALL('Table'))
return
IF(cumulative_sales <= total_sales * 0.8, cumulative_sales)``````

I think this is the result you want:

Best regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

Hi @NanDeb ,

In order to better understanding your demands and give the right solution, could you please tell me what's your expected output?

Thanks for your efforts & time in advance.

Best regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper I

A simplied example of by data here:

 TaxDate CustID PARTID VALUE 01/01/2022 ZZZ1 23 345356.1 02/01/2022 BBB2 45 2468.87 03/01/2022 GGG1 76 12479.56 04/01/2022 ZZZ1 23 1358.8 05/01/2022 AAA5 23 2357.45 06/01/2022 AAA9 87 196246.7 07/01/2022 CCC2 45 3463.87 08/01/2022 VVV1 67 3666.67 09/01/2022 BBB2 76 234.98 10/01/2022 ZZZ1 12 2352.87 11/01/2022 CCC9 12 6345.56 12/01/2022 EEE6 56 34653.78

 Id CustID 1 AAA5 2 AAA9 3 BBB2 4 CCC2 5 CCC9 6 EEE9 7 GGG1 8 VVV1 9 ZZZ1

 Id PartID 1 23 2 45 3 76 4 87 5 67 6 12 7 56

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

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