cancel
Showing results for
Did you mean: Frequent Visitor

## TOP N with condition on another measure

Hello Guys,

I need your help, because  I a have a fact table like this : I need to identify the product with the best average "Note", but only with products sold over to 50 units (sum of quantity).

With the summarize/addcolumns, it's easy to retrieve for each product the both information : "average note" & "total quantity": As, you can see, the TOP1 Product, with the best average note AND with more of 50 units sold is Product C.

Please, can you help me to find the correct formula to apply the top 1 on the "Summarize table" but with the filter on the "Total Quantity"  (or another method...) ?

For information, I have a fact table with a lot of dimension (date, product, region, Supplier etc...) and the formula need to work with all slicer in the report.

1 ACCEPTED SOLUTION  Community Champion

try this code to create a new table:

``````Table 2 =
VAR _A =
FILTER (
SUMMARIZE (
'Table',
'Table'[Product],
"Sum(QTY)", SUM ( 'Table'[QTY] ),
"Average(Note)", AVERAGE ( 'Table'[Note] )
),
[Sum(QTY)] > 50
)
RETURN
ADDCOLUMNS ( _A, "rank", RANKX ( _A, [Average(Note)],, DESC ) )``````

Output: Or if you need all Products in the new table, use this code:

``````Table 2 =
VAR _A =
SUMMARIZE (
'Table',
'Table'[Product],
"Sum(QTY)", SUM ( 'Table'[QTY] ),
"Average(Note)", AVERAGE ( 'Table'[Note] )
)
RETURN
_A,
"rank",
IF (
[Sum(QTY)] > 50,
RANKX ( FILTER ( _A, [Sum(QTY)] > 50 ), [Average(Note)],, DESC ),
BLANK ()
)
)``````

Output: If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

2 REPLIES 2 Frequent Visitor

Thank you Vahid. I just added a filter (Rank = 1 => in order to push the TOP1 Product in a card) and an "allselected" to update the ranking according to the use of a slicer product.  Community Champion

try this code to create a new table:

``````Table 2 =
VAR _A =
FILTER (
SUMMARIZE (
'Table',
'Table'[Product],
"Sum(QTY)", SUM ( 'Table'[QTY] ),
"Average(Note)", AVERAGE ( 'Table'[Note] )
),
[Sum(QTY)] > 50
)
RETURN
ADDCOLUMNS ( _A, "rank", RANKX ( _A, [Average(Note)],, DESC ) )``````

Output: Or if you need all Products in the new table, use this code:

``````Table 2 =
VAR _A =
SUMMARIZE (
'Table',
'Table'[Product],
"Sum(QTY)", SUM ( 'Table'[QTY] ),
"Average(Note)", AVERAGE ( 'Table'[Note] )
)
RETURN
_A,
"rank",
IF (
[Sum(QTY)] > 50,
RANKX ( FILTER ( _A, [Sum(QTY)] > 50 ), [Average(Note)],, DESC ),
BLANK ()
)
)``````

Output: If this post helps, please consider accepting it as the solution to help the other members find it more quickly.   