Need Assistance on Dax to achieve requirement

[lavankumar1989a]

Hi All,

Hope all are doing great!

Need your assistance on Power BI requirement. Sample data as below

Business Area	STATUS	Measure 3
MASTER DATA - MATERIAL	Failed	19.61%
MASTER DATA - MATERIAL	In Progress	185.51%
MASTER DATA - MATERIAL	Successfully Completed	81.76%
OPERATIONS MASTER DATA	Failed	19.61%
OPERATIONS MASTER DATA	Successfully Completed	105.13%
OPERATIONS TRANSACTIONS	Failed	19.61%
OPERATIONS TRANSACTIONS	Successfully Completed	58.70%
PURCHASING MASTER DATA	Failed	19.61%
PURCHASING TRANSACTIONS	Failed	19.61%
PURCHASING TRANSACTIONS	Successfully Completed	54.38%

Coming to requirement is need to derive a column for measure3 based on status like,

if Status=”Successfully Completed” then Measure3=100%

If Status=”Inprogress/failed” then measure3>100% then value as 99% else measure3 value.

Based on above conditions expected output like below,

Business Area	STATUS	Measure 3
MASTER DATA - MATERIAL	Failed	19.61%
MASTER DATA - MATERIAL	In Progress	99.00%
MASTER DATA - MATERIAL	Successfully Completed	100.00%
OPERATIONS MASTER DATA	Failed	19.61%
OPERATIONS MASTER DATA	Successfully Completed	100.00%
OPERATIONS TRANSACTIONS	Failed	19.61%
OPERATIONS TRANSACTIONS	Successfully Completed	100.00%
PURCHASING MASTER DATA	Failed	19.61%
PURCHASING TRANSACTIONS	Failed	19.61%
PURCHASING TRANSACTIONS	Successfully Completed	100.00%

Based on above output looking for final sample outcome expecting by customer like below,

Business Area	STATUS	% Completion
MASTER DATA - MATERIAL	Failed	72.87%	(19.61+99+100)/3
OPERATIONS MASTER DATA	Failed	59.80%	(19.80+100)/2

Kindly help me on to achieve the requirement. Really much appriciate your efforts in advance.

Thanks,

Lavan

[amitchandak]

@lavankumar1989a , Assuming Measure3 was a measure not a column

If it % column value around 1

AverageX(summarize(Table, Table[Business Area], Table[Status] , "_1", Switch( True(), max(Table[Status]) ="Successfully Completed" ,1 ,
max(Table[Status]) ="Inprogress/failed" && [Measure 3] >1 , .99 ,
[Measure 3])), [_1])

If it number column value around 100
AverageX(summarize(Table, Table[Business Area], Table[Status] , "_1", Switch( True(), max(Table[Status]) ="Successfully Completed" ,100 ,
max(Table[Status]) ="Inprogress/failed" && [Measure 3] >100 , 99 ,
[Measure 3])), [_1])