Forum Discussion
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 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 |
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 attachedIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
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.
4 Replies
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 attachedIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
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.
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
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
