DAX - What % of Months hit the Target

[Sharma0815]

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

Dept	surveyID	Survey	MM-YY
Marketing	23412	TOP2	Nov-20
Marketing	23413	TOP2	Nov-20
Marketing	23414	TOP2	Nov-20
Marketing	23415	TOP2	Nov-20
Marketing	23416	TOP2	Nov-20
Marketing	23417	TOP2	20-Dec
Marketing	23418	TOP2	20-Dec
Marketing	23419	TOP2	20-Dec
Marketing	23420	Bottom3	20-Dec
Marketing	23421	Bottom3	20-Dec
Marketing	23422	Bottom3	20-Dec
Marketing	23423	Bottom3	20-Dec
Marketing	23424	Bottom3	20-Dec
Marketing	23425	Bottom3	20-Dec
Marketing	23426	Bottom3	20-Dec
Marketing	23427	TOP2	Jan-21
Marketing	23428	TOP2	Jan-21
Marketing	23429	TOP2	Jan-21
Marketing	23430	TOP2	Jan-21
Marketing	23431	TOP2	Jan-21
Marketing	23432	TOP2	Jan-21
Marketing	23433	Bottom3	Jan-21
Marketing	23434	Bottom3	Jan-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

Dept	Target
Marketing	70
Sales	60
IT	55
Tech	80

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

[Sharma0815]

Thanks alot @Jihwan_Kim 

[Jihwan_Kim · Accepted solution]

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.

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

)

https://www.dropbox.com/s/41x2npmi7w38xto/sharma.pbix?dl=0 

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