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...
  • tamerj1's avatar
    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.