cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VGuichard
Frequent Visitor

TOP N with condition on another measure

Hello Guys,

 

I need your help, because  I a have a fact table like this :

VGuichard_0-1634679082847.png

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":

VGuichard_1-1634679344098.png

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.

 

Thanks in advance.

1 ACCEPTED SOLUTION
VahidDM
Community Champion
Community Champion

Hi @VGuichard 

 

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:

 

VahidDM_0-1634695152989.png

 

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
    ADDCOLUMNS (
        _A,
        "rank",
            IF (
                [Sum(QTY)] > 50,
                RANKX ( FILTER ( _A, [Sum(QTY)] > 50 ), [Average(Note)],, DESC ),
                BLANK ()
            )
    )

 

Output:

VahidDM_1-1634695344823.png

 

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

Appreciate your Kudos!!

 

View solution in original post

2 REPLIES 2
VGuichard
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.

VahidDM
Community Champion
Community Champion

Hi @VGuichard 

 

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:

 

VahidDM_0-1634695152989.png

 

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
    ADDCOLUMNS (
        _A,
        "rank",
            IF (
                [Sum(QTY)] > 50,
                RANKX ( FILTER ( _A, [Sum(QTY)] > 50 ), [Average(Note)],, DESC ),
                BLANK ()
            )
    )

 

Output:

VahidDM_1-1634695344823.png

 

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

Appreciate your Kudos!!

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.