cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Get the common value using calculate

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

1 ACCEPTED SOLUTION
Microsoft Employee

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

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

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

2 REPLIES 2
Microsoft Employee

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

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

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Super User

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 e= topn(20,d,[dishcount],DESC)
var r= CONCATENATEX(e,[dish] & format([percentage]," (#%)"),",")
return r``````

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors