How can I create a calculator that pulls values from two table visuals?

[cs7238]

I have a data table that breaks down different mock food products by their recipes and the percent contribution of each ingredient to each the recipe. Here is a snippet of what this table looks like (I will post a screenshot of the full table at the end of this post):

Block	Product	Product Spec Code	Spec Code	Spec Description	Percent Contribution	Leavening Agent	Category	Format	Brand	Gluten Free	Leavening System
1	Food Brand 1 (Plain Pancakes)	11111	11111	Food Brand 1 (Plain Pancakes)	100	N	Pancake	Plain	Food Brand 1	N	10%: Eggs, 6%: Baking powder
1	Food Brand 1 (Plain Pancakes)	11111	ING1	Flour	50	N
1	Food Brand 1 (Plain Pancakes)	11111	ING6	Milk	18	N
1	Food Brand 1 (Plain Pancakes)	11111	ING7	Eggs	10	Y
1	Food Brand 1 (Plain Pancakes)	11111	ING2	Sugar	8	N
1	Food Brand 1 (Plain Pancakes)	11111	ING5	Vegetable oil	7	N
1	Food Brand 1 (Plain Pancakes)	11111	ING3	Baking powder	6	Y
1	Food Brand 1 (Plain Pancakes)	11111	ING4	Salt	1	N
2	Food Brand 1 (Vanilla Pancakes)	22222	22222	Food Brand 1 (Vanilla Pancakes)	100	N	Pancake	Liquid Mix In	Food Brand 1	N	12%: Eggs, 7%: Baking powder
2	Food Brand 1 (Vanilla Pancakes)	22222	ING1	Flour	45	N
2	Food Brand 1 (Vanilla Pancakes)	22222	ING6	Milk	16	N
2	Food Brand 1 (Vanilla Pancakes)	22222	ING7	Eggs	12	Y
2	Food Brand 1 (Vanilla Pancakes)	22222	ING2	Sugar	10	N
2	Food Brand 1 (Vanilla Pancakes)	22222	ING3	Baking powder	7	Y
2	Food Brand 1 (Vanilla Pancakes)	22222	ING5	Vegetable oil	6	N
2	Food Brand 1 (Vanilla Pancakes)	22222	ING8	Vanilla	3	N
2	Food Brand 1 (Vanilla Pancakes)	22222	ING4	Salt	1	N

This is what my power BI dashboard looks like right now (explanation below):

The search bars at the top are text filters that will cause only their corresponding table visual (the table visual directly below) to display ingredients from the product with that product spec code. The slicer below each table lists the spec description of each ingredient in the product searched. The two slicers are synced so that both table visuals will be sliced by the one spec code selected, and only display the percent contribution for that ingredient (spec description). Here is what it looks like when I use a slicer to display only "Sugar" in the table visuals, and what the drop-down menu looks like:

I would like to create a calculator using DAX that uses the two values in those table visuals to calculate percent similarity between the two percent contribution numbers. The percent similarity formula I am using is: % similarity = ((2*(A*B))/(A+B))*100. I'd also like the calculated percent similarity to appear in a card visual below the two slicers. Finally, I think it's important to mention that the slicers are synced and there is only one data table that both of these table visuals are pulling data from.

As promised, here are screenshots of the full data table:

If there's anything you'd like clarified, please let me know and I'll do my best to explain better! Thank you so much for your help.

[pcoley]

I understand you're looking to work with ingredient percentages in your data. 
Here are two approaches:
1. **Pre-processing:** If possible, transform your data before loading it into Power BI. Split the "Percent Contribution" column into individual ingredient and percentage columns. This will make direct DAX calculations much simpler.
2. **DAX for Unpivoting (More Advanced):** If you can't pre-process, you might consider a DAX approach using `UNION` and calculated columns to unpivot your data, but this can become complex.

[v-saisrao-msft · Accepted solution]

Hi @cs7238,

I was able to reproduce your scenario using sample data and achieved the expected similarity calculation.

I’ve attached the PBIX file for your reference

Thank you.

.

[123abc]

Well designed ... excellent 👌

[cs7238]

I am running into an issue where only some of the ingredients appear in the table when I use your solution. I copy-pasted all of the DAX measures/tables into my powerBI. The only way to make all ingredients show is to add "Product Spec Code" to the table visual. I set "Summarization" to "Don't summarize" for Percent Contribution and Product Spec Code so that it would display Percent Contribution and not "sum of" in the table visual, which brought back some of the ingredients but not all.

[v-saisrao-msft]

Hi @cs7238,

Consider using a measure-based approach rather than filtering the table. You can apply the updated DAX measures provided below and use them in your table visuals instead of the Percent Contribution column.

Value A =
VAR SelectedSpec1 =
SELECTEDVALUE('Spec Selector 1'[Spec Code #1])
RETURN
CALCULATE(
MAX(RecipeData[Percent Contribution]),
RecipeData[Product Spec Code] = SelectedSpec1
)

Value B =
VAR SelectedSpec2 =
SELECTEDVALUE('Spec Selector 2'[Spec Code #2])
RETURN
CALCULATE(
MAX(RecipeData[Percent Contribution]),
RecipeData[Product Spec Code] = SelectedSpec2
)

Thank you.

[pauliinah]

Measures never pull values from visuals, only from the data model and filter context. So even though you have two table visuals, you are still filtering the one data table. For the filter context, if the two slicers are using the same column from the data table, they form an AND condition. What you could do is create two disconnected tables for the two slicers with the spec codes and descriptions and use DAX to get the values you need. So the model would look something like this:

And the DAX measures something like this:

DEFINE
    MEASURE 'Spec Code 1'[Percent Contribution Spec 1] = VAR productspec = SELECTEDVALUE('Spec Code 1'[Product Spec Code])
VAR spec = SELECTEDVALUE('Spec Code 1'[Spec Description])
RETURN
CALCULATE(
    MAX(Data[Percent Contribution]),
    Data[Product Spec Code] = productspec,
    Data[Spec Description] = spec
)
    MEASURE 'Spec Code 2'[Percent Contribution Spec 2] = VAR productspec = SELECTEDVALUE('Spec Code 2'[Product Spec Code])
VAR spec = SELECTEDVALUE('Spec Code 2'[Spec Description])
RETURN
CALCULATE(
    MAX(Data[Percent Contribution]),
    Data[Product Spec Code] = productspec,
    Data[Spec Description] = spec
)
    MEASURE 'Data'[% Similarity] = VAR A = [Percent Contribution Spec 1]
VAR B = [Percent Contribution Spec 2]
RETURN
DIVIDE( 2 * ( A * B ), ( A + B ) )