cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lavankumar1989a
Frequent Visitor

Need Assistance on Dax to achieve requirement

Hi All,

 

Hope all are doing great!

 

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

Business AreaSTATUSMeasure 3
MASTER DATA - MATERIALFailed19.61%
MASTER DATA - MATERIALIn Progress185.51%
MASTER DATA - MATERIALSuccessfully Completed81.76%
OPERATIONS MASTER DATAFailed19.61%
OPERATIONS MASTER DATASuccessfully Completed105.13%
OPERATIONS TRANSACTIONSFailed19.61%
OPERATIONS TRANSACTIONSSuccessfully Completed58.70%
PURCHASING MASTER DATAFailed19.61%
PURCHASING TRANSACTIONSFailed19.61%
PURCHASING TRANSACTIONSSuccessfully Completed54.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 AreaSTATUSMeasure 3
MASTER DATA - MATERIALFailed19.61%
MASTER DATA - MATERIALIn Progress99.00%
MASTER DATA - MATERIALSuccessfully Completed100.00%
OPERATIONS MASTER DATAFailed19.61%
OPERATIONS MASTER DATASuccessfully Completed100.00%
OPERATIONS TRANSACTIONSFailed19.61%
OPERATIONS TRANSACTIONSSuccessfully Completed100.00%
PURCHASING MASTER DATAFailed19.61%
PURCHASING TRANSACTIONSFailed19.61%
PURCHASING TRANSACTIONSSuccessfully Completed100.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

1 REPLY 1
amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors