Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How to multiply a measure with another table values?

Hi!

I have a table 1 like this, where all the columns are measures:

(its an example i created in excel bc of sensitive data)

 

A slicer like this:

 

And another table 2 like this:

(this one I get from live data)

 

If i select the Iphone X model in the slicer, I need to multiply the value of the table 1, "%Qty Local" column, "Battery" row, by the value of the table 2, on the Score column, "Iphone X" model and "Battery" commodity. 

     Example:

I select the "Iphone X" model on the slicer.

On the table 1 the value of the commodity "Processor" in the measure "% Qty Local" is 97.64%.

On the table 2 the score of the commodity "Processor" of the "Iphone X" is 3.

I want a function that allow me to show the value of (97.64% x 3= 2.93) and all the other values of the commodities of "Iphone X" like this:

 

Could anyone help me with this? (sorry for my grammar, English it's not my first language)

 

Need Help
  • elitesmitpatel's avatar
    elitesmitpatel
    2 years ago

    1)Create a Measure to Retrieve the Selected Model from the Slicer

    First, create a measure to get the selected model from the slicer.

     

    measure =

    SelectedModel = SELECTEDVALUE(Models[Model])

     

    2) Create a Measure to Retrieve the Score from Table 2

    Next, create a measure to get the score for the selected model and commodity from Table 2.

     

    ScoreValue =
    CALCULATE(
    MAX(Scores[Score]),
    Scores[Model] = [SelectedModel],
    Scores[Commodity] = MAX(Table1[Commodity]) -- Assuming Table1 has a column named Commodity
    )

     

    3)Create a Measure to Perform the Multiplication

    FinalValue =
    VAR QtyLocal = MAX(Table1[%Qty Local]) / 100 -- Convert percentage to a decimal
    VAR Score = [ScoreValue]
    RETURN QtyLocal * Score

     

    4) 

    Create a Visual to Display the Results

    Add a table or matrix visual to your report and add the commodity  column from Table 1 along with the  FinalValue measure. This will show the multiplied results for each commodity based on the selected model.

     

    If you can't get the answer please share the PBIX file for reference.

     

2 Replies

  • elitesmitpatel's avatar
    elitesmitpatel
    Icon for Solution Supplier rankSolution Supplier
    2 years ago

    1)Create a Measure to Retrieve the Selected Model from the Slicer

    First, create a measure to get the selected model from the slicer.

     

    measure =

    SelectedModel = SELECTEDVALUE(Models[Model])

     

    2) Create a Measure to Retrieve the Score from Table 2

    Next, create a measure to get the score for the selected model and commodity from Table 2.

     

    ScoreValue =
    CALCULATE(
    MAX(Scores[Score]),
    Scores[Model] = [SelectedModel],
    Scores[Commodity] = MAX(Table1[Commodity]) -- Assuming Table1 has a column named Commodity
    )

     

    3)Create a Measure to Perform the Multiplication

    FinalValue =
    VAR QtyLocal = MAX(Table1[%Qty Local]) / 100 -- Convert percentage to a decimal
    VAR Score = [ScoreValue]
    RETURN QtyLocal * Score

     

    4) 

    Create a Visual to Display the Results

    Add a table or matrix visual to your report and add the commodity  column from Table 1 along with the  FinalValue measure. This will show the multiplied results for each commodity based on the selected model.

     

    If you can't get the answer please share the PBIX file for reference.

     

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    2 years ago

    Hi Anonymous ,

     

    You can produce your required output in the following way.  

    1) Create measures to multiply the 'table 2'[scores] for the respective commodities by the local quantity % measure.

    Processor =
SUMX (
    'table 2',
    IF (
        'table 2'[Commodity] = "Processor",
        [Score points]
            * CALCULATE ( [% Qty Local], KEEPFILTERS ( 'Table'[Commodity] = "Processor" ) )
    )
)

     

     

    Battery =
SUMX (
    'table 2',
    IF (
        'table 2'[Commodity] = "Battery",
        [Score points]
            * CALCULATE ( [% Qty Local], KEEPFILTERS ( 'Table'[Commodity] = "Battery" ) )
    )
)

     

    and so on... and combine those commodity multiplication measures in a single measure like below:

    Commodity Local points =
[Battery] + [Processor] + [Memory] + [Screen] + [Speaker]

    Then put that combined measure in a matrix visual next to the commodify field and apply the model slicer as shown below:

     

    You can confirm that the resultant output above is producing the same calculated result as your instruction.  

    I attach an example pbix file.  

    Best regards,