Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm having trouble writing a measure to flag items that are part of 80% of cumulative total of quantity sold.
Some background information...I work for a manufacturer that acts as a supplier to distributors. We have around 30,000 items that we offer to distributors. The items have our item number (parent product) and have our distributor's item number (child product). I've been asked to find the distributors that sell our items that are not selling as well as other distributors who sell the same product.
For example, we offer "Product A" to 15 different disributors and in the table below you can see there are 2-3 distributors that sell a large amount of the product and the remaining 12 distributors are quite a bit lower.
Parent Product | Child Product | Distributor | Sales Amount | Sales Quantity |
Product A | Product A1 | Distributor 1 | 538269.14 | 458 |
Product A | Product A3 | Distributor 3 | 644912.62 | 444 |
Product A | Product A5 | Distributor 5 | 240091.6 | 164 |
Product A | Product A7 | Distributor 7 | 86204.82 | 68 |
Product A | Product A9 | Distributor 9 | 49950.74 | 38 |
Product A | Product A11 | Distributor 11 | 25497.17 | 21 |
Product A | Product A13 | Distributor 13 | 21104.4 | 16 |
Product A | Product A14 | Distributor 14 | 13144.07 | 11 |
Product A | Product A15 | Distributor 15 | 10468.03 | 8 |
Product A | Product A10 | Distributor 10 | 2949.5 | 2 |
Product A | Product A12 | Distributor 12 | 2190.34 | 2 |
Product A | Product A8 | Distributor 8 | 1708.84 | 1 |
Product A | Product A2 | Distributor 2 | 0 | 0 |
Product A | Product A4 | Distributor 4 | 0 | 0 |
Product A | Product A6 | Distributor 6 | 0 | 0 |
The company prefers to analyze things by quantity sold rather than sales dollars.
I'm not sure the best way to analyze this data but my idea is to take the cumulative total quantity sold and find the number of distributors that make up 80% of the total quantity sold and categorize them as "A" and the rest as "B" (this rank could be called Distributor Support Rank). This would allow users to focus on the "B" items and find out why sales are lower for those distributors.
With that idea in mind, the target visual would be a table that has Parent Product, Child Product, Distributor, Sales Amount, Sales Quantity, and Distributor Support Rank (see the belowo table for the visual). The Qty Sold Rank and Qty Sold Cumulative Pct columns would just be helper measures to help calculate the Distributor Support Rank and are not required for displaying in the visual.
Parent Product | Child Product | Distributor | Sales Amount | Sales Quantity | Qty Sold Rank | Qty Sold Cumulative Pct | Distributor Support Rank |
Product A | Product A1 | Distributor 1 | 538269.14 | 458 | 1 | 0.371451744 | A |
Product A | Product A3 | Distributor 3 | 644912.62 | 444 | 2 | 0.731549067 | A |
Product A | Product A5 | Distributor 5 | 240091.6 | 164 | 3 | 0.864557989 | B |
Product A | Product A7 | Distributor 7 | 86204.82 | 68 | 4 | 0.919708029 | B |
Product A | Product A9 | Distributor 9 | 49950.74 | 38 | 5 | 0.95052717 | B |
Product A | Product A11 | Distributor 11 | 25497.17 | 21 | 6 | 0.9675588 | B |
Product A | Product A13 | Distributor 13 | 21104.4 | 16 | 7 | 0.98053528 | B |
Product A | Product A14 | Distributor 14 | 13144.07 | 11 | 8 | 0.98945661 | B |
Product A | Product A15 | Distributor 15 | 10468.03 | 8 | 9 | 0.99594485 | B |
Product A | Product A10 | Distributor 10 | 2949.5 | 2 | 10 | 0.99756691 | B |
Product A | Product A12 | Distributor 12 | 2190.34 | 2 | 11 | 0.99918897 | B |
Product A | Product A8 | Distributor 8 | 1708.84 | 1 | 12 | 1 | B |
Product A | Product A2 | Distributor 2 | 0 | 0 | 13 | 1 | B |
Product A | Product A4 | Distributor 4 | 0 | 0 | 14 | 1 | B |
Product A | Product A6 | Distributor 6 | 0 | 0 | 15 | 1 | B |
I'm having trouble authoring the measures to calculate the Qty Sold Rank, Qty Sold Cumulative Pct, and the Distributor Support Rank.
Below is my attempt at the Qty Sold Rank measure. The "Item[No_]" field is the Child Product field. I can't attempt to write the other measures until this one is working.
Any help would be appreciated, thanks!
Solved! Go to Solution.
Hi, @Drew89
Thank you very much for your reply. Maybe you can try the earlier. Here are the DAX expressions:
EVALUATE
VAR _summary =
SUMMARIZECOLUMNS (
'Table'[Parent Product],
'Table'[Child Product],
"sales amount", SUMX ( 'Table', 'Table'[Sales Amount] ),
"sales Qty", SUMX ( 'Table', 'Table'[Sales Quantity] )
)
VAR _addrank =
ADDCOLUMNS ( _summary, "rank", RANKX ( _summary, [sales Qty],, DESC, DENSE ) )
VAR _rollingtotal =
ADDCOLUMNS (
_addrank,
"QTY RT", SUMX ( FILTER ( _addrank, [rank] <= EARLIER ( [rank] ) ), [sales amount] )
)
RETURN
_rollingtotal
Here are the results:
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Drew89
Based on your description, I first created an Index column in Power Query, as shown in the following image:
I use the following DAX expression to classify distributors:
MEASURE =
VAR _table =
SUMMARIZE (
'Table',
'Table'[Parent Product],
'Table'[Child Product],
'Table'[Sales Amount],
'Table'[Sales Quantity],
'Table'[Index],
'Table'[Distributor],
"columns",
VAR _total_quantity =
CALCULATE ( SUM ( 'Table'[Sales Quantity] ), ALL ( 'Table' ) )
VAR _cumulitive_quantity =
CALCULATE (
SUM ( 'Table'[Sales Quantity] ),
FILTER ( ALL ( 'Table' ), 'Table'[Index] <= EARLIER ( 'Table'[Index] ) )
)
RETURN
DIVIDE ( _cumulitive_quantity, _total_quantity )
)
RETURN
IF(ISINSCOPE('Table'[Parent Product]),IF ( MAXX ( _table, [columns] ) <= 0.8, "A", "B" ))
The results are as follows:
I've uploaded the PBIX file I used this time below.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I have the query to get the rank working in Dax Studio but I can't seem to use the rank to help calculate the rolling total.
Below is my query for coming up with the rank by the Sales Qty.
Below is the query extended to compute the rolling total but I'm getting the error "OrderBy column 'QtyRank' could not be resolved." I've not been succcessful in finding out how to fix this issue.
Hi, @Drew89
Thank you very much for your reply. Maybe you can try the earlier. Here are the DAX expressions:
EVALUATE
VAR _summary =
SUMMARIZECOLUMNS (
'Table'[Parent Product],
'Table'[Child Product],
"sales amount", SUMX ( 'Table', 'Table'[Sales Amount] ),
"sales Qty", SUMX ( 'Table', 'Table'[Sales Quantity] )
)
VAR _addrank =
ADDCOLUMNS ( _summary, "rank", RANKX ( _summary, [sales Qty],, DESC, DENSE ) )
VAR _rollingtotal =
ADDCOLUMNS (
_addrank,
"QTY RT", SUMX ( FILTER ( _addrank, [rank] <= EARLIER ( [rank] ) ), [sales amount] )
)
RETURN
_rollingtotal
Here are the results:
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you, this does look like it will work however I need the index/ranking to by calculated dynamically in DAX because I have multiple years of data and the ranking can change depending on the dates selected in slicers or filters.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |