Calculating the ratio of ingredients using measures only

Anonymous — Sat, 28 Oct 2023 06:38:40 GMT

So I have the following problem and I need help with it using Dax measures only. The data looks like this:

Recipe	Ingredient	Cuisine
Hummus	Chickpeas	Syrian
Hummus	Tahini	Syrian
Hummus	Oil	Syrian
Hummus	Chickpeas	Lebanese

I want to calculate the ratio of ingredients to recipes depending on in how many cuisines do they appear together.
For example: the result should first be like:

Recipe	Ingredient	# Cuisines
Hummus	Chickpeas	2
Hummus	Tahini	1
Hummus	Oil	1

And then finally the ratio of ingredients to recipes should be 1.33 since it's the sum of cuisines divided by number of ingredients.

But the biggest problem is that some of these rows are duplicates since there are other columns as well. Because of that I could not simply take the distinct count of cuisine and I ended up with a ratio of 0.66 finally. I hope I am being clear enough here.

How could I achieve this using measures only?

Thanks in advance.

Re: Calculating the ratio of ingredients using measures only

Dangar332 — Sat, 28 Oct 2023 09:13:30 GMT

Hi, Anonymous

try below code just adjust your table and column name.

result = var a = CALCULATE(COUNT('table 1'[Cuisine]),ALL('table 1')) var b = CALCULATE(DISTINCTCOUNT('table 1'[Ingredient]),ALLEXCEPT('table 1','table 1'[Recipe])) return divide(a,b)

topic Re: Calculating the ratio of ingredients using measures only in DAX Commands and Tips

Calculating the ratio of ingredients using measures only

Re: Calculating the ratio of ingredients using measures only