Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I'm trying to create a measure that shows the best selling product per market.
However, when I inserted my measure it's giving each market the same product.
Any idea where I'm going wrong?
Code below and PBIX attached.
Many Thanks in advance.
https://drive.google.com/file/d/1ZDfNmrZ60eMfjB1WU3cSMcOGrQqkPt7X/view?usp=drive_link
Solved! Go to Solution.
What in case you have multiple items with same sales per market? You could use following formula that will concatenate such items:
Best Selling product per market v2 =
VAR summarizedTable = // Creates virtual table of sales per item
ADDCOLUMNS(
DISTINCT('Dimension_Item Table'[Item Description]), // DISTINCT gets rid of blank rows
"@Sales", CALCULATE(SUM('Sales Table'[Amount]))
)
VAR maxSales = // Gets max sales
MAXX(summarizedTable, [@Sales])
VAR filteredTable = // Returns all the rows with max sales
FILTER(summarizedTable, [@Sales] = maxSales)
VAR result = // Concatenate in case there are multiple items with same sales
CONCATENATEX(
filteredTable,
'Dimension_Item Table'[Item Description],
","
)
RETURN {result}
I think it should work as per your requirements:
I included comments but let me know if you have questions. TOPN can be resource expensive with large data. This formula is quite fast.
I am not sure I follow you Chain related question. When I use the chain from your model it works fine:
As for the question to bring the top sales as well, you could use the same code but till "maxSales" variable, like here:
Top Sales =
VAR summarizedTable = // Creates virtual table of sales per item
ADDCOLUMNS(
DISTINCT('Dimension_Item Table'[Item Description]), // DISTINCT gets rid of blank rows
"@Sales", CALCULATE(SUM('Sales Table'[Amount]))
)
VAR maxSales = // Gets max sales
MAXX(summarizedTable, [@Sales])
RETURN {maxSales}
And here is the result:
Sure, here the DAX...
TopProductAmountByMarket =
CALCULATE(
SUM('Sales Table'[Amount]),
FILTER(
'Dimension_Item Table',
'Dimension_Item Table'[Item Description] = [TopProductByMarket]
)
)
Proud to be a Super User!
What in case you have multiple items with same sales per market? You could use following formula that will concatenate such items:
Best Selling product per market v2 =
VAR summarizedTable = // Creates virtual table of sales per item
ADDCOLUMNS(
DISTINCT('Dimension_Item Table'[Item Description]), // DISTINCT gets rid of blank rows
"@Sales", CALCULATE(SUM('Sales Table'[Amount]))
)
VAR maxSales = // Gets max sales
MAXX(summarizedTable, [@Sales])
VAR filteredTable = // Returns all the rows with max sales
FILTER(summarizedTable, [@Sales] = maxSales)
VAR result = // Concatenate in case there are multiple items with same sales
CONCATENATEX(
filteredTable,
'Dimension_Item Table'[Item Description],
","
)
RETURN {result}
I think it should work as per your requirements:
I included comments but let me know if you have questions. TOPN can be resource expensive with large data. This formula is quite fast.
That worked thank you!
One more quick question. Is there a way to be able to drag in the correct amount instead of the overall amount as per my screenshot?
Also i nmy data I'm trying to get the best selling item per chain. Some of the chains have no items sold so as a result it's throwing out all the products instead of being blank. Is there a way to fix this also?
I am not sure I follow you Chain related question. When I use the chain from your model it works fine:
As for the question to bring the top sales as well, you could use the same code but till "maxSales" variable, like here:
Top Sales =
VAR summarizedTable = // Creates virtual table of sales per item
ADDCOLUMNS(
DISTINCT('Dimension_Item Table'[Item Description]), // DISTINCT gets rid of blank rows
"@Sales", CALCULATE(SUM('Sales Table'[Amount]))
)
VAR maxSales = // Gets max sales
MAXX(summarizedTable, [@Sales])
RETURN {maxSales}
And here is the result:
Thank you so much for your help, it's much appreciated!
Here's the updated code...
CALCULATE(
FIRSTNONBLANK('Dimension_Item Table'[Item Description], SUM('Sales Table'[Amount])),
FILTER(
ALLNOBLANKROW('Dimension_Item Table'),
RANKX(
ALLNOBLANKROW('Dimension_Item Table'),
CALCULATE(SUM('Sales Table'[Amount])),
,
DESC,
Dense
) = 1
)
)
Proud to be a Super User!
Hi Mustafa,
Thank you so much for the code. That worked.
Is there a way to be able to drag in the correct amount for the best selling product also?
Sure, here the DAX...
TopProductAmountByMarket =
CALCULATE(
SUM('Sales Table'[Amount]),
FILTER(
'Dimension_Item Table',
'Dimension_Item Table'[Item Description] = [TopProductByMarket]
)
)
Proud to be a Super User!
Thank you so much, that worked!
Thank you!
Try the updated DAX...
Best Selling Product per Market NEW =
VAR TopProductByMarket =
TOPN(
1,
VALUES('Dimension_Market Table'[Market]),
CALCULATE(
SUM('Sales Table'[Amount]),
ALL('Dimension_Item Table')
),
DESC
)
RETURN
FIRSTNONBLANK(
'Dimension_Item Table'[Item Description],
CALCULATE(
SUM('Sales Table'[Amount]),
TopProductByMarket
)
)
Results.
Proud to be a Super User!
I tried your code and still seem to be getting the incorrect best selling product.
User | Count |
---|---|
80 | |
74 | |
62 | |
61 | |
46 |
User | Count |
---|---|
108 | |
97 | |
88 | |
81 | |
61 |