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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors