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
Super User
Super User

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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Sharma0815
Helper II
Helper II

Thanks alot @Jihwan_Kim 

Jihwan_Kim
Super User
Super User

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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors