Forum Discussion

santoshlearner2's avatar
santoshlearner2
Resolver II
2 years ago
Solved

Top Product name

Dear All,   Stalwarts please help i just cannot get it. This i want for Top 1 & TOp 2 in card visuals. Pls note the results are based on the date selection and the measure for computing the sales f...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi santoshlearner2 
    You can use the attached measures for the calculations :

    Total Sales SUM('Table'[Sales])
    Max Sales MAXX('table', [Total Sales])
    Product with Max Sales

    VAR MaxSales = [Max Sales]

    RETURN

    CALCULATE(

        FIRSTNONBLANK('table'[Main Product], 1),

        FILTER('table', [Total Sales] = MaxSales)

    )
    Store with Max Sales

    VAR MaxSales = [Max Sales]

    RETURN

    CALCULATE(

        FIRSTNONBLANK('table'[Store], 1),

        FILTER('table', [Total Sales] = MaxSales)

    )
    Second Max Sales

    VAR MaxSales = [Max Sales]

    RETURN

    MAXX(

        FILTER(

            'table',

            [Total Sales] < MaxSales

        ),

        [Total Sales]

    )
    Product with Second Max Sales

    VAR SecondMaxSales = [Second Max Sales]

    RETURN

    CALCULATE(

        FIRSTNONBLANK('table'[Main Product], 1),

        FILTER('table', [Total Sales] = SecondMaxSales)

    )
    Store with Second Max Sales

    VAR SecondMaxSales = [Second Max Sales]

    RETURN

    CALCULATE(

        FIRSTNONBLANK('table'[Store], 1),

        FILTER('table', [Total Sales] = SecondMaxSales)

    )

    And then concatenate the results according your needs.
    The pbix is attached 

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

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi，

    Thanks for the solution Ritaf1983  offered, and i want to offer some more information for user to refer to.

    hello santoshlearner2 , you can refer to the following solution.

    The sample data is the same as you provided, create the following measures.

     

    Sales = SUM('Table'[Final sales])
    Top1 =
VAR a =
    TOPN (
        2,
        SUMMARIZE (
            ALLSELECTED ( 'Table' ),
            [Store],
            'Table'[Main Product],
            'Table'[Sub Product],
            [Date],
            "Sales", [Sales]
        ),
        [Sales], DESC
    )
VAR b =
    MAXX ( a, [Sales] )
RETURN
    CONCATENATEX (
        FILTER ( a, [Sales] = b ),
        [Store] & "(" & [Sub Product] & " " & "$" & [Sales] & ")"
    )

    Top2 =
VAR a =
    TOPN (
        2,
        SUMMARIZE (
            ALLSELECTED ( 'Table' ),
            [Store],
            'Table'[Main Product],
            'Table'[Sub Product],
            [Date],
            "Sales", [Sales]
        ),
        [Sales], DESC
    )
VAR b =
    MINX ( a, [Sales] )
RETURN
    CONCATENATEX (
        FILTER ( a, [Sales] = b ),
        [Store] & "(" & [Sub Product] & " " & "$" & [Sales] & ")"
    )

     

    Then put the top measures to the card visual.

    Output

     

    Best Regards!

    Yolo Zhu

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

Ritaf1983's avatar
Ritaf1983
Super User
2 years ago

Hi santoshlearner2 
You can use the attached measures for the calculations :

Total Sales SUM('Table'[Sales])
Max Sales MAXX('table', [Total Sales])
Product with Max Sales

VAR MaxSales = [Max Sales]

RETURN

CALCULATE(

    FIRSTNONBLANK('table'[Main Product], 1),

    FILTER('table', [Total Sales] = MaxSales)

)
Store with Max Sales

VAR MaxSales = [Max Sales]

RETURN

CALCULATE(

    FIRSTNONBLANK('table'[Store], 1),

    FILTER('table', [Total Sales] = MaxSales)

)
Second Max Sales

VAR MaxSales = [Max Sales]

RETURN

MAXX(

    FILTER(

        'table',

        [Total Sales] < MaxSales

    ),

    [Total Sales]

)
Product with Second Max Sales

VAR SecondMaxSales = [Second Max Sales]

RETURN

CALCULATE(

    FIRSTNONBLANK('table'[Main Product], 1),

    FILTER('table', [Total Sales] = SecondMaxSales)

)
Store with Second Max Sales

VAR SecondMaxSales = [Second Max Sales]

RETURN

CALCULATE(

    FIRSTNONBLANK('table'[Store], 1),

    FILTER('table', [Total Sales] = SecondMaxSales)

)

And then concatenate the results according your needs.
The pbix is attached 

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