Pass rate by groups and by date filters

[Rambo92]

Hi, I need help on this for creating a measure to calculate the pass percentage on the sample Audit data.

I need the pass percentage of eac Area where,

0 - No issue,

1 - deduction in 3.5% passrate

2 - deduction in 7% passrate 

3 - deduction in 10% passrate

The desired output should be something like below.

If Production filter is applied and date as 09/01/2022 - Passrate (100% - 21%) which gives 79%.

If two Areas selected Production and Storage for 09/01/2022 - Passrate (100% - average of both is 17.5%) which gives 82.5%.

Thanks in advance.

[bolfri]

For your unpivoted data:

Passrate =
var lowering = SUMX(SampleData,
SWITCH(SampleData[Value],
1,0.035,
2,0.07,
3,0.1,
0
)
)
var number_of_areas = DISTINCTCOUNT(SampleData[Area])
var average_lowering = DIVIDE(lowering,number_of_areas)
return IF(NOT(ISBLANK(average_lowering)),1-average_lowering)

Results:

[Rambo92]

Hi bolfri,

Thanks for your time.

It is showing the correct required output if one date is selected. But not showing the average pass rate if all dates or multiple dates are selected. 

[bolfri]

That's why you should tell me what is correct output value for multiple dates. Is is the AVERAGE SUM per Area?

[Rambo92]

It should show 91.3% if all dates selected.

[bolfri · Accepted solution]

Ok, then to simplify the code we will add new column with Value logic:

Value logic = 
    SWITCH(SampleData[Value],
        1,0.035,
        2,0.07,
        3,0.1,
        0
    )

And the measure will be like this:

Passrate v2 = 
var lowering_table = SUMMARIZE(SampleData,SampleData[Area],SampleData[Date],"SUM",SUM(SampleData[Value logic]))
var lowering_value = AVERAGEX(lowering_table,[SUM])
return IF(NOT(ISBLANK(lowering_value)),1-lowering_value)

Result:

[Rambo92]

This is what i am looking for...Thanks a lot mate..

[jaweher899]

Please try 

Pass Percentage:= CALCULATE( 100 - (SUMX(FILTER(TBL_Sample,TBL_Sample[Issue]=1),TBL_Sample[Passrate])*3.5 + SUMX(FILTER(TBL_Sample,TBL_Sample[Issue]=2),TBL_Sample[Passrate])*7 + SUMX(FILTER(TBL_Sample,TBL_Sample[Issue]=3),TBL_Sample[Passrate])*10) /COUNT(TBL_Sample), FILTER(TBL_Sample,TBL_Sample[Area]=SELECTEDVALUE(TBL_Sample[Area])) )

[Rambo92]

After importing data into PowerBI, I have unpivotted all date columns. So this is how it looks now.

Please see above screenshot for reference.

Thanks

[Rambo92]

Thanks for your reply jaweher899.

Should i need to create a new column with Passrate which is multiplied with the value.