Top Product name

[santoshlearner2]

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 figures

Measure to get Top 1 Values with the sub product and amount

Store: Name of the Stores

Main Product: Main Product Basket, which is further divided int sub product

Sub Product: Divisions of Main Products.

Daily Sales; Referred as [total sales]),

Measure  for top 1 Category =

CONCATENATEX (

    TOPN ( 1, VALUES ( sub product]),  [total sales]),

    sub product]),  & " - Rs " & format([total sales]), 0" & "  ($) "),

    ";"

)

The above gives me the product name along with the respective amount , this is in card visuals, now if I want to get name of the Stores for the above measure how do I get it, like Store 1 ( Name and value), store 2 Name and value ( in card visual)

Similarly of the above I want for Second Rank also.

Data is like this

Main Product	Sub Product	Store	Final sales  is from a measure	Date
Bike	Tyres	Delhi	100	31-01-2024
Bike	Chain	mumbai	154	31-01-2024
Bike	Bucket	goa	160	31-01-2024
Car	Windshield	bandra	210	31-01-2024
Car	Accessories	Khar	230	31-01-2024
Car	Pencil	Nagpur	260	31-01-2024
Rulers	Small	North	100	31-01-2024
Rulers	Big	South	154	31-01-2024
Rulers	Medium	Chennai	160	31-01-2024
Bike	Tyres	Delhi	210	30-01-2024
Bike	Chain	mumbai	230	30-01-2024
Bike	Bucket	goa	260	30-01-2024
Car	Windshield	bandra	100	30-01-2024
Car	Accessories	Khar	110	30-01-2024
Car	Pencil	Nagpur	155	30-01-2024
Rulers	Small	North	156	30-01-2024
Rulers	Big	South	210	30-01-2024
Rulers	Medium	Chennai	260	30-01-2024
Bike	Tyres	Delhi	78	28-01-2024
Bike	Chain	mumbai	452	28-01-2024
Bike	Bucket	goa	460	28-01-2024
Car	Windshield	bandra	450	28-01-2024
Car	Accessories	Khar	136	28-01-2024
Car	Pencil	Nagpur	156	28-01-2024
Rulers	Small	North	850	28-01-2024
Rulers	Big	South	1230	28-01-2024
Rulers	Medium	Chennai	120	28-01-2024

[Ritaf1983 · Accepted solution]

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

[santoshlearner2]

Dear Power BI Greats,

Wasted weeks to find a solution  just not getting it. I want to create card like this , wanted card visuals

1) For eg if a person selects a date, then in the card, the name of the  top selling product should be displayed alongwith the amount,  then in the next to it i have another card which will show which is the store who sold maximum units on that date for that  product, like this i have two more cards showing top 2 and top 3 names of the stores ( for that product) for eg

Byke sales : Rs 10 cr, then Store 1 - Rs 50 lacs, store 2: 15 lacs, store 3: 10 lacs

2) Then i want to show second best selling product, which will in a visual card, then similar to point one in card visual the name and amount of the store selling the maximum units of that products

Car sales : Rs 3 cr, then Store 1 - Rs 45 lacs, store 2: 25 lacs, store 3: 15 lacs.

Basicaly i have measure which does daily computation of price / units sold based on each day values

You may suggest Any other method to show,

Thanks in advance to everyone,

Thank you

[hackcrr]

Hi, @santoshlearner2 

Can you provide some simple sample data that does not contain private data? So that you can write the corresponding DAX expression according to your table structure. In addition, you can refer to the following tutorial to get Top N, and then you can use & to connect the store name and sales corresponding to Top N.

https://community.fabric.microsoft.com/t5/Desktop/card-with-top-product-by-sales/td-p/2510766

https://community.fabric.microsoft.com/t5/Desktop/Card-with-two-Top-N-FIlters/m-p/3871591


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

[v-xinruzhu-msft · Accepted solution]

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 · Accepted solution]

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