Forum Discussion

cs7238's avatar
cs7238
Regular Visitor
4 months ago
Solved

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

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 ...
  • v-saisrao-msft's avatar
    v-saisrao-msft
    4 months ago

    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.

    .

v-saisrao-msft's avatar
v-saisrao-msft
Icon for Community Support rankCommunity Support
4 months ago

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's avatar
    123abc
    Icon for Community Champion rankCommunity Champion
    4 months ago

    Well designed ... excellent 👌

  • cs7238's avatar
    cs7238
    Regular Visitor
    4 months ago

    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's avatar
      v-saisrao-msft
      Icon for Community Support rankCommunity Support
      4 months ago

      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.