Measure for labeling items part of 80% of cumulative total quantity sold

[Drew89]

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!

[v-jianpeng-msft]

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.

[Drew89]

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.

Quantity Rank - Working

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.

Quantity Rolling Total - Not Working

[v-jianpeng-msft · Accepted 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.

[Drew89]

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.