Forum Discussion
Multiply two measures column
- 4 years ago
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 ) - 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:- From the format settings > activate row totals.
- 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.
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 )Hi amitchandak ,
Thank you, its work. But it is appear 2 duplicated column as below
Is there any possibility to remove the circle column?
- tamerj14 years ago
Community Champion
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:- From the format settings > activate row totals.
- 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.
- norazlina02104 years agoRegular Visitor
Hi tamerj1 ,
I prefer to hide the duplicated column instead, thanks for your advices. Really appreciate it 😃