Multiply two measures column

[norazlina0210]

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 · Accepted solution]

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 · Accepted solution]

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]

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 · Accepted solution]

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.

[norazlina0210]

Hi @tamerj1 ,

I prefer to hide the duplicated column instead, thanks for your advices. Really appreciate it 😃

[amitchandak]

@norazlina0210 ,Try a measure like

Productx(Values(Table[Type]), [Yield Rate])

[norazlina0210]

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])