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

1 ACCEPTED SOLUTION
Community Support

Hi @NanDeb ，

``````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 ，

``````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?

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

New forum boards available in Real-Time Intelligence.

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

Power BI Monthly Update - May 2024

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

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors