Get the common value using calculate

[Anonymous]

Hello,

I have a restaurant dashboard where I have a table that contains each food along with its type, and I have to show for each type, a card with the dish that is most requested with its percentage.

Example: Spaghetti (75%) Lemon Juice (70%)

dish_name	id dish type	dish type
spaghetti	1	main dish
spaghetti	1	main dish
spaghetti	1	main dish
icecream	2	dessert
lemon juice	3	drink
hamburger	1	main dish
water	3	drink
lemon juice	3	drink

Taking the example of spaghetti which corresponds to the main dish type, I have a measurement (maximum main course quantity) that returns the number of appearances.

What I need is that instead of having the number of appearances, have a measure that is the most requested main dish= Spaghetti.

Anyone know how to solve it? Thank you very much

[mahoneypat · Accepted solution]

Here is one way to do it with a measure expression.

Top Dish =
CONCATENATEX (
    TOPN ( 1, VALUES ( Menu[dish_name] ), CALCULATE ( COUNTROWS ( Menu ) ), DESC ),
    Menu[dish_name],
    " "
)

If you put it in a table visual with the dish type column, this is the result

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[lbendlin]

Here's something to get you started.  it uses copious amounts of variables to show each step of the process.

This will only work if you show the dish type  grouped, and it will produce weird results for the totals.  i'll leave that up to you.

distribution = 
var a = distinct(Menu[dish_name])
var b = ADDCOLUMNS(a,"dish",Menu[dish_name],"total",count(Menu[dish_name]))
var c = ADDCOLUMNS(b,"dishcount",CALCULATE(count(Menu[dish_name]),filter(Menu,Menu[dish_name]=[dish])))
var d = ADDCOLUMNS(c,"percentage",DIVIDE([dishcount],[total]))
var e= topn(20,d,[dishcount],DESC)
var r= CONCATENATEX(e,[dish] & format([percentage]," (#%)"),",")
return r