Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Power BI superuser,
I have problem with my Matrix table below:
The Yield Rate is the measures value with Dax as below:
Solved! Go to Solution.
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 @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:
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?
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:
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.
Hi @tamerj1 ,
I prefer to hide the duplicated column instead, thanks for your advices. Really appreciate it 😃
@norazlina0210 ,Try a measure like
Productx(Values(Table[Type]), [Yield Rate])
Hi @amitchandak ,
I have tried, but it comes out as below, did i do something wrong?
My dax as below:
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
86 | |
84 | |
52 | |
37 | |
23 |