Forum Discussion

norazlina0210's avatar
norazlina0210
Regular Visitor
4 years ago
Solved

Multiply two measures column

Hi Power BI superuser,

 

I have problem with my Matrix table below:

 

The Yield Rate is the measures value with Dax as below:

Yield Rate = 1-(SUM('BTE Raw Data'[Rejected Qty.]) / SUM('BTE Raw Data'[Inspection Qty.]))
 
My problems is how to multiply Yield Rate Acoustic and Yield Rate Listening become single value named Overall Yield Rate?
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi norazlina0210 

    you can try

    Overall Yield Rate =
VAR AcousticRate =
    CALCULATE (
        DIVIDE (
            SUM ( 'BTE Raw Data'[Rejected Qty.] ),
            SUM ( 'BTE Raw Data'[Inspection Qty.] )
        ),
        'BTE Raw Data'[Taype] = "ACOUSTIC TEST"
    )
VAR ListeningRate =
    CALCULATE (
        DIVIDE (
            SUM ( 'BTE Raw Data'[Rejected Qty.] ),
            SUM ( 'BTE Raw Data'[Inspection Qty.] )
        ),
        'BTE Raw Data'[Taype] = "LISTENING"
    )
RETURN
    ( 1 - AcousticRate ) * ( 1 - ListeningRate )
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi norazlina0210 
    Actually I was waiting you to ask me for this 🙂 
    Yes there is a way but is not perfect. This would be using row totals as follows:

    1. From the format settings > activate row totals.
    2. Modify the existing measure [Yield Rate] as follows:
    Yield Rate =
VAR YieldRate =
    DIVIDE (
        SUM ( 'BTE Raw Data'[Rejected Qty.] ),
        SUM ( 'BTE Raw Data'[Inspection Qty.] )
    )
VAR AcousticRate =
    CALCULATE (
        DIVIDE (
            SUM ( 'BTE Raw Data'[Rejected Qty.] ),
            SUM ( 'BTE Raw Data'[Inspection Qty.] )
        ),
        'BTE Raw Data'[Taype] = "ACOUSTIC TEST"
    )
VAR ListeningRate =
    CALCULATE (
        DIVIDE (
            SUM ( 'BTE Raw Data'[Rejected Qty.] ),
            SUM ( 'BTE Raw Data'[Inspection Qty.] )
        ),
        'BTE Raw Data'[Taype] = "LISTENING"
    )
RETURN
    IF (
        HASONEVALUE ( 'BTE Raw Data'[Taype] ),
        1 - YieldRate,
        ( 1 - AcousticRate ) * ( 1 - ListeningRate )
    )

    Now the problem would be that there will be two totals. One for [Yield Rate] and one for [Inspection Quantity] which might not make sense to you. but If it does based on whatever logic then we can apply this logic the [Inspection Quantity] measure's formula same as we did with the [Yield Rate] measure. Otherwise, we can just blank out the values of the total or just hide the column manually. Please advise how you would like to proceed.

6 Replies

    • norazlina0210's avatar
      norazlina0210
      Regular Visitor
      4 years ago

      Hi amitchandak ,

       

      I have tried, but it comes out as below, did i do something wrong?

       

      My dax as below:

      Measure = Productx(Values('BTE Raw Data'[Type]), [Yield Rate])
  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi norazlina0210 

    you can try

    Overall Yield Rate =
VAR AcousticRate =
    CALCULATE (
        DIVIDE (
            SUM ( 'BTE Raw Data'[Rejected Qty.] ),
            SUM ( 'BTE Raw Data'[Inspection Qty.] )
        ),
        'BTE Raw Data'[Taype] = "ACOUSTIC TEST"
    )
VAR ListeningRate =
    CALCULATE (
        DIVIDE (
            SUM ( 'BTE Raw Data'[Rejected Qty.] ),
            SUM ( 'BTE Raw Data'[Inspection Qty.] )
        ),
        'BTE Raw Data'[Taype] = "LISTENING"
    )
RETURN
    ( 1 - AcousticRate ) * ( 1 - ListeningRate )
    • norazlina0210's avatar
      norazlina0210
      Regular Visitor
      4 years ago

      Hi amitchandak ,

       

      Thank you, its work. But it is appear 2 duplicated column as below

      Is there any possibility to remove the circle column? 

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Hi norazlina0210 
        Actually I was waiting you to ask me for this 🙂 
        Yes there is a way but is not perfect. This would be using row totals as follows:

        1. From the format settings > activate row totals.
        2. Modify the existing measure [Yield Rate] as follows:
        Yield Rate =
VAR YieldRate =
    DIVIDE (
        SUM ( 'BTE Raw Data'[Rejected Qty.] ),
        SUM ( 'BTE Raw Data'[Inspection Qty.] )
    )
VAR AcousticRate =
    CALCULATE (
        DIVIDE (
            SUM ( 'BTE Raw Data'[Rejected Qty.] ),
            SUM ( 'BTE Raw Data'[Inspection Qty.] )
        ),
        'BTE Raw Data'[Taype] = "ACOUSTIC TEST"
    )
VAR ListeningRate =
    CALCULATE (
        DIVIDE (
            SUM ( 'BTE Raw Data'[Rejected Qty.] ),
            SUM ( 'BTE Raw Data'[Inspection Qty.] )
        ),
        'BTE Raw Data'[Taype] = "LISTENING"
    )
RETURN
    IF (
        HASONEVALUE ( 'BTE Raw Data'[Taype] ),
        1 - YieldRate,
        ( 1 - AcousticRate ) * ( 1 - ListeningRate )
    )

        Now the problem would be that there will be two totals. One for [Yield Rate] and one for [Inspection Quantity] which might not make sense to you. but If it does based on whatever logic then we can apply this logic the [Inspection Quantity] measure's formula same as we did with the [Yield Rate] measure. Otherwise, we can just blank out the values of the total or just hide the column manually. Please advise how you would like to proceed.