Rank orders by value with multiple lines per order

[ebianco]

Hello community, hope you can help me out ... 

I've been trying to create a ranking of sales orders where some of them can have multiple line in the fact table since a given order can contain multiple products.

I have a really simple model, a fact table SalesOrders with two dimensions Date and Products, and two disconnected tables one for measures and one for rank grouping:

This is what my fact table looks like:

Then I created the following measures:

(credit to Enterprise DNA since I took this formula from them ... )

As a result of the measures above, I get the following working correctly:

So far so good ... the problem is what I want to add Products into the table or matrix and still see how the orders rank. As you can see in the table below, it is my DAX formula is ranking the orders within each product category / name:

All that said, I really don't know how to modify my measure to have the ranking be like in the first set of tables but still show the product level data in the table... expecting something like this:

The top 3 orders continue to be 10, 9 and 5, but I'm now showing the products each has with their values.

Hope the explanation of my problem and what I've done so far was clear. I'm also adding the pbix file for this example.

Sample pbix file 

I really appreciate any help you can provide.

Thanks

Esteban

[wdx223_Daniel]

is this what you want?

[ebianco]

Yes, that is what I was looking for .. the issue I have now is that if I filter for a given product, say Product A, I would expected OrderNo 10, 9 and 4 to show up as Top 3 (given OrderNo 3 does not contain Product A), however this solution it is only showing my Orders 10 and 9.

[daxer-almighty]

If you want to make the calculation also relative to the selected products, you have to change ALLEXCEPT( ..., 'Date' ) to ALLEXCEPT( ..., 'Date', 'Product' ) in the formula.

[ebianco]

Thanks for your continued help @daxer-almighty . However when I do that, I get an odd behavior .. check image below:

Thanks

Esteban

[daxer-almighty · Accepted solution]

@ebianco

You can try this one. I suspect it's now doing what you wanted...

Sales BG2 = 
var CurrentlyVisibleGroups = DISTINCT( 'Order Groups'[Group] )
var CurrentlyVisibleOrders = DISTINCT( SalesOrders[OrderNo] )
var OrdersInCurrentlyVisibleGroups =
    CALCULATETABLE(
        var AllOrders = DISTINCT( SalesOrders[OrderNo] )
        return
        FILTER(
            CurrentlyVisibleOrders,
            var OrderRank = 
                RANKX(
                    AllOrders,
                    [Sales],,
                    DESC
                )
            var OrderGroup =
                MAXX(
                    FILTER(
                        'Order Groups',
                        'Order Groups'[Min] < OrderRank
                        && 
                        OrderRank <= 'Order Groups'[Max]
                    ),
                    'Order Groups'[Group]
                )
            return
                OrderGroup in CurrentlyVisibleGroups
        ),
        ALLEXCEPT( SalesOrders, 'Date' ),
        // This directive should let you only rank
        // against orders that are relative to
        // all the visible products, visible
        // somewhere in your visual, not necessarily
        // just in the cell being evaluated.
        ALLSELECTED( 'Products' )
    )
var Result =
    CALCULATE(
        [Sales],
        OrdersInCurrentlyVisibleGroups
    )
return
    Result

Bear in mind that 'Products' must be a dimension connected to your fact table 'SalesOrders'. And you should never slice in the UI by the columns of your fact tables. Only via dimensions. If you don't follow this rule... you'll be in trouble sooner or later. I tell you today.

[ebianco]

@daxer-almighty that almost did it .. I think I got it fixed by changing the ALLEXCEPT( SalesOrders, 'Date' ) for ALLSELECTED( SalesOrders).

Thanks for all your help.

[daxer-almighty]

Good you got it working exactly as you wanted. Sorry I was not able to address this problem immediately but I did not fully understand how this measure should behave in all possible contexts. 

[ebianco]

You did great and put me on the right direction .. thanks for your help.

[daxer-almighty]

Sales BG2 = 
var CurrentlyVisibleGroups = DISTINCT( 'Order Groups'[Group] )
var CurrentlyVisibleOrders = DISTINCT( SalesOrders[OrderNo] )
// Need to find all those orders
// from the visible ones that fall
// into any of the visible order groups.
var OrdersInCurrentlyVisibleGroups =
    CALCULATETABLE(
        var AllOrders = DISTINCT( SalesOrders[OrderNo] )
        return
        FILTER(
            CurrentlyVisibleOrders,
            var OrderRank = 
                RANKX(
                    AllOrders,
                    [Sales],,
                    DESC
                )
            var OrderGroup =
                MAXX(
                    // MAXX is OK here as the filer will
                    // return at most one row if everything
                    // is correctly set up.
                    FILTER(
                        'Order Groups',
                        'Order Groups'[Min] < OrderRank
                        && 
                        OrderRank <= 'Order Groups'[Max]
                    ),
                    'Order Groups'[Group]
                )
            return
                OrderGroup in CurrentlyVisibleGroups
        ),
        // If you want to make this ranking independent of
        // Date as well, just use ALL instead of ALLEXCEPT.
        ALLEXCEPT( SalesOrders, 'Date' )
    )
var Result =
    CALCULATE(
        [Sales],
        OrdersInCurrentlyVisibleGroups
    )
return
    Result

[ebianco]

Thank you very much for the quick reply @daxer-almighty !

Question about this solution ... I understand you first pull the CurrentlyVisibleOrders based on the filter context applied in the report, and then you iterate through AllOthers to rank them and return those that are within each group.

However if I change the filter context in my report, e.g. filtering only those orders that contain a given product, the formula still evaluates all orders and but only shows them if they have the product.

Thanks

Esteban