Top Customers by Rate - Filter for customers before ranking

[Anonymous]

I am trying to get the top 10 customers by return rate, within the list of customers, I want the customers who have at least 100 ordered units shipped. The date filter is for the current fiscal year

Here is my calculation for the top 10 customers by return rate YTD. This formula works/is correct, but I would like this ranking to apply to only customers who have ordered at least 100 units of materials in the fiscal year.

Top 10 Customers by Return Rate YTD = CALCULATE([Measure Return Rate YTD], FILTER(VALUES(Customer[CustomerName]),

IF(

RANKX(

ALL(Customer[CustomerName]), [Measure Return Rate YTD],, DESC) <= 10, [Measure Return Rate YTD], BLANK()

)

)

)

I modified the calculation to include variables for the customers who have ordered at least a 100 units of product. The calcualtion is right below.

Top 10 Customers by Return Rate YTD - Customers with at least 100 units ordered YTD =

VAR T1 =

    SUMMARIZE(

        FILTER(Orders,

            Orders[ShipDate] >= (IF(MONTH(TODAY())>=7,DATE(YEAR(TODAY()), 07, 01),DATE(YEAR(TODAY())-1,07, 01))) && Orders[ShipDate] <= DATE(YEAR(TODAY()), MONTH(TODAY()),DAY(TODAY()))),

        -- Group by---

        Orders[Customer Name Lookup],

        -- Count materials by customer

        "Ordered units shipped", SUM(Orders[Quantity])

    )

VAR T2 =

    FILTER(

        T1,

        [Ordered units shipped] > 99

    )

RETURN

CALCULATE(

    [Return Rate YTD],

        FILTER(T2,

            IF(RANKX(ALL(Customer[CustomerName]), [Return Rate YTD],, DESC) <= 10, [Return Rate YTD], BLANK()

            )

        )

This calculation does not return the top 10 return rates by customer (factoring the 100 units shipped/ordered miminum), it returns the full list of return rates. I'm not sure why or how to go about applying this filter in the RANKX calculation, to return the correct information.

[v-lionel-msft]

Hi @Anonymous ,

I can't find a logical error in your formula.

Since I didn't see your model, I couldn't find the problem.

You can try to write the formula separately, first create a measure to sort the records , then return table T2,  and finally create a measure to return to the top 10. This way you can verify that the value returned at each step is correct.

Like this (I just give an example):

Ordered units shipped = 

T2 = 
    SUMMARIZE(

        FILTER(
            Orders,
            Orders[ShipDate] >= (IF(MONTH(TODAY())>=7,DATE(YEAR(TODAY()), 07, 01),DATE(YEAR(TODAY())-1,07, 01))) && Orders[ShipDate] <= DATE(YEAR(TODAY()), MONTH(TODAY()),DAY(TODAY())) && [Ordered units shipped] > 99
        ),
        Orders[Customer Name Lookup],
        "Ordered units shipped", SUM(Orders[Quantity])
)

Top 10 Customers by Return Rate YTD = 
CALCULATE(
    [Measure Return Rate YTD], 
    FILTER(
        T2,
        IF(
            RANKX( ALL(Customer[CustomerName]), [Measure Return Rate YTD],, DESC) <= 10, 
            [Measure Return Rate YTD], BLANK() 
        )
    )
)

Maybe you can show a sample data model, which is more intuitive.

Best regards,
Lionel Chen

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

[Anonymous]

Thank you for responding. I've included additonal information.

Table Layout of Orders table 

ShipDate	OrdType	SalesRegion	CustNum	MatNum	ShipQty	UnitsShippedID	Customer Name Lookup
Tuesday, November 19, 2019	TA	Z5	1	123	3	3637501	CompA
Tuesday, November 19, 2019	TA	Z5	2	456	4	3637509	CompB
Tuesday, November 19, 2019	TA	Z5	1	123	4	3637503	CompA
Wednesday, November 13, 2019	TA	Z5	2	567	6	3624878	CompB
Wednesday, November 13, 2019	TA	Z5	1	543	10	3626351	CompA

I tried creating a measure summarizing the orders quantity by customer name:

T2 =
SUMMARIZE(
FILTER(
Orders,
Orders[ShipDate] >= (IF(MONTH(TODAY())>=7,DATE(YEAR(TODAY()), 07, 01),DATE(YEAR(TODAY())-1,07, 01))) &&Orders[ShipDate] <= DATE(YEAR(TODAY()), MONTH(TODAY()),DAY(TODAY()))
),
Orders[Customer Name Lookup],
"Ordered units shipped", SUM(Orders[ShipQty])
)

But I got an DAX error stating, "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Try the formula:

T2 = 
VAR x = 
IF(
    MONTH(TODAY()) >= 7,
    DATE(YEAR( TODAY() ), 7, 1),
    DATE( YEAR( TODAY() )-1, 7, 1)
)
VAR y = 
SUMMARIZE(
    FILTER(
        Orders,
        Orders[ShipDate] >= x && Orders[ShipDate] <= TODAY()
    ),
    Orders[Customer Name Lookup],
    "Ordered units shipped", SUM(Orders[ShipQty])
)
RETURN
SELECTCOLUMNS(
    FILTER(
        y,
        // In your data table, you should set [Ordered units shipped] >= 100
        [Ordered units shipped] >=10
    ),
    "Customer Name Lookup", [Customer Name Lookup],
    "Ordered units shipped", [Ordered units shipped]
)

// Measures
_Max = MAX(T2[Ordered units shipped])
_Rank = 
RANKX(
    ALL(T2),
    [_Max],
    , ASC, Dense
)

Finally, you can add a filter in the visual.

Best regards,
Lionel Chen

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