Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
marmaghan
Frequent Visitor

Conditional Division

Hi, I am stuck in a situation please guide me 

in below mention data which Have ID targets and achievement of KPI's I have create a pivot table as mention below and have created three measures

 

=sum(Table1[Tgt])
=sum(Table1[Ach])
=DIVIDE([Ach1],[Target],0)*100

now I need to check Ach % in a way that in all KPI if >=120 should be 120 else same division except E KPI it should be check on if >=105 overwrite it to  105 else same division. Raw data is mention below thanks.

 

Untitled.png

 

 

Pivot Table Raw Data

 Column Labels                     ABCDEFGRow LabelsTargetAch1Ach%TargetAch1Ach%TargetAch1Ach%TargetAch1Ach%TargetAch1Ach%TargetAch1Ach%TargetAch1Ach%Grand Total28303366119070151556127145789739964592676241542355905966221046386137380979183484410591

MonthAll                    
                      
6002378372980008326035843366310135882602910900818377939121336781347610812990
60067888751110103762622337261201991863121716374726882232321042531124113211241024104990
6013711878123000444283783874988887673359559834208910215315010161608419966760194090
601595312411300606117549161344987100169736901627330996188160851927142115754811089298794

 

 Raw Data

 

Date|ID|KPI|MONTH|TGT|Ach

01/05/2018 00:006002AMay378372
01/05/2018 00:006006AMay788875
01/05/2018 00:006013AMay711878
01/05/2018 00:006015AMay9531241
01/05/2018 00:006002BMay00
01/05/2018 00:006006BMay01
01/05/2018 00:006013BMay00
01/05/2018 00:006015BMay06
01/05/2018 00:006002CMay83260358433663
01/05/2018 00:006006CMay3762622337261201
01/05/2018 00:006013CMay4442837838749888
01/05/2018 00:006015CMay6117549161344987
01/05/2018 00:006002DMay35882602910900
01/05/2018 00:006006DMay1863121716374726
01/05/2018 00:006013DMay67335955983420
01/05/2018 00:006015DMay1697369016273309
01/05/2018 00:006002EMay8377
01/05/2018 00:006006EMay223232
01/05/2018 00:006013EMay102153
01/05/2018 00:006015EMay188160
01/05/2018 00:006002FMay912
01/05/2018 00:006006FMay2531
01/05/2018 00:006013FMay1016
01/05/2018 00:006015FMay1927
01/05/2018 00:006002GMay67813476108129
01/05/2018 00:006006GMay1132112410241049
01/05/2018 00:006013GMay84199667601940
01/05/2018 00:006015GMay1157548110892987
1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @marmaghan,

 

Based on your data you shared, we can create a measure to achieve your goal.

 

Measure = IF(MAX(Table1[KPI])="E"&& [Ach%]>=105,105,IF([Ach%]>=120 && MAX(Table1[KPI])<>"E",120,[Ach%]))

Capture.PNG

Also please find the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

2 REPLIES 2
v-frfei-msft
Community Support
Community Support

Hi @marmaghan,

 

Based on your data you shared, we can create a measure to achieve your goal.

 

Measure = IF(MAX(Table1[KPI])="E"&& [Ach%]>=105,105,IF([Ach%]>=120 && MAX(Table1[KPI])<>"E",120,[Ach%]))

Capture.PNG

Also please find the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Thanks for reply it is working fine now

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.