cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sharma0815
Helper II
Helper II

DAX - What % of Months hit the Target

Hi All,

I am trying to create a measure to calculate what % of months that TOP2 surveys hit my target.

Here is my sample dataset.

Dept_Surveys

DeptsurveyIDSurveyMM-YY
Marketing23412TOP2Nov-20
Marketing23413TOP2Nov-20
Marketing23414TOP2Nov-20
Marketing23415TOP2Nov-20
Marketing23416TOP2Nov-20
Marketing23417TOP220-Dec
Marketing23418TOP220-Dec
Marketing23419TOP220-Dec
Marketing23420Bottom320-Dec
Marketing23421Bottom320-Dec
Marketing23422Bottom320-Dec
Marketing23423Bottom320-Dec
Marketing23424Bottom320-Dec
Marketing23425Bottom320-Dec
Marketing23426Bottom320-Dec
Marketing23427TOP2Jan-21
Marketing23428TOP2Jan-21
Marketing23429TOP2Jan-21
Marketing23430TOP2Jan-21
Marketing23431TOP2Jan-21
Marketing23432TOP2Jan-21
Marketing23433Bottom3Jan-21
Marketing23434Bottom3Jan-21

 

My marketing got 100% TOP2 surveys in Nov-2020, 30% in Dec-2020, and 75% in Jan-2021

 

I have one more table which contains the actual targets for each department.

 

Dept_Targets

 

DeptTarget
Marketing70
Sales60
IT55
Tech80

 

So here, my marketing dept achieved the target in Nov-2020 and Jan-2021,I need to calculate What % of months actually hit the target.

the measure should be like Div ( IF % of TOP2 surveys > 70 then distinct Distinctcount(MM-YY) / Distinctcount(MM-YY))

Thanks in Advance



1 ACCEPTED SOLUTION
Jihwan_Kim
Community Champion
Community Champion

Hi, @Sharma0815 

Please check the below picture and the sample pbix file's link down below.

I am not sure if I understood your question correctly, but I tried to extend the sample and created the extended version like below.

 

Picture1.png

 

Survey Count =
COUNTROWS ( Survey )
 
Top2 survey count =
CALCULATE ( COUNTROWS ( Survey ), Survey[Survey] = "TOP2" )
 
 
Months percent hit target =
VAR targets =
MAX ( Target[Target] )
VAR newtable =
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Survey, Survey[YYYY-MM] ),
"@top2survey", [Top2 survey count],
"@surveyall", [Survey Count]
),
[@top2survey] / [@surveyall] >= targets
)
RETURN
IF (
ISFILTERED ( Target[Dept] ),
COUNTROWS ( newtable ) / COUNTROWS ( VALUES ( Survey[YYYY-MM] ) )
)
 
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

2 REPLIES 2
Sharma0815
Helper II
Helper II

Thanks alot @Jihwan_Kim 

Jihwan_Kim
Community Champion
Community Champion

Hi, @Sharma0815 

Please check the below picture and the sample pbix file's link down below.

I am not sure if I understood your question correctly, but I tried to extend the sample and created the extended version like below.

 

Picture1.png

 

Survey Count =
COUNTROWS ( Survey )
 
Top2 survey count =
CALCULATE ( COUNTROWS ( Survey ), Survey[Survey] = "TOP2" )
 
 
Months percent hit target =
VAR targets =
MAX ( Target[Target] )
VAR newtable =
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Survey, Survey[YYYY-MM] ),
"@top2survey", [Top2 survey count],
"@surveyall", [Survey Count]
),
[@top2survey] / [@surveyall] >= targets
)
RETURN
IF (
ISFILTERED ( Target[Dept] ),
COUNTROWS ( newtable ) / COUNTROWS ( VALUES ( Survey[YYYY-MM] ) )
)
 
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

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

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates