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: ...
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.

     

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,