topic Re: Rank orders by value with multiple lines per order in DAX Commands and Tips

Rank orders by value with multiple lines per order

Anonymous — Sat, 28 Aug 2021 03:11:04 GMT

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

Re: Rank orders by value with multiple lines per order

daxer-almighty — Sat, 28 Aug 2021 22:42:35 GMT

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

Re: Rank orders by value with multiple lines per order

Anonymous — Mon, 30 Aug 2021 15:39:52 GMT

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

Re: Rank orders by value with multiple lines per order

wdx223_Daniel — Tue, 31 Aug 2021 03:09:03 GMT

is this what you want?

Re: Rank orders by value with multiple lines per order

Anonymous — Tue, 31 Aug 2021 13:59:55 GMT

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.

Re: Rank orders by value with multiple lines per order

daxer-almighty — Tue, 31 Aug 2021 17:58:34 GMT

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.

Re: Rank orders by value with multiple lines per order

Anonymous — Tue, 31 Aug 2021 18:25:24 GMT

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

Thanks

Esteban

Re: Rank orders by value with multiple lines per order

daxer-almighty — Wed, 01 Sep 2021 07:56:32 GMT

Anonymous

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.

Re: Rank orders by value with multiple lines per order

Anonymous — Wed, 01 Sep 2021 14:54:51 GMT

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.

Re: Rank orders by value with multiple lines per order

daxer-almighty — Wed, 01 Sep 2021 20:24:30 GMT

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.

Re: Rank orders by value with multiple lines per order

Anonymous — Wed, 01 Sep 2021 20:29:59 GMT

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