tag based on total of sum by each resource

[Sahury]

Hi All! I've just begun learning PBI and my background is mostly Excel. I know in my heart this formula/query might be simple but I just haven't been able to make it work. I'm trying to "label" each sum result according to either above or below 1 to know the offenders on time tracking in my team.

My ideal matrix visual would look like this, the whole page is filtered by current month so the "offense" formula would have to be dinamic? or integrate filters? (I've tried this with calculate-sumx-filter withour sucess)

Resource	Time	Offense
Name 1	0.3	Incomplete
Name 2	1.5	Overtime
Name 3	(blank)	Missing

The matrix is composed of one list from sharepoint, there are many columns but this are the important ones. Resource ID has a relationship with the Header list that brings other info like management. 

1. Detail

Project	Resource	Resource ID	Month	Time
Project1	Name 1	1	10/2/2019	0
Project2	Name 1	1	10/1/2019	0.1
Project3	Name 1	1	10/14/2019	0.5
Project1	Name 2	2	10/5/2019	1

You can see a single person can be in many projects and for each month enter different time but we are really trying to label the offense based on every month's total.

This is some of what I've tried so far by creating a Measure I can add to the matrix... but it does not work or it labels everything "incomplete". Appreciate any help since my deadline is near

Var sumar =
CALCULATE(
SUM(FTEDetail[Actual]),
FILTER(FTEDetail,FTEDetail[MonthFTE]=MONTH(TODAY())),
FILTER(FTEDetail,SELECTEDVALUE(FTEDetail[ResourceID])
)
Var tag = IF(sumar>1,"Overtime",IF(sumar=1,"Complete",IF(sumar<1,"Incomplete","Missing")))

return tag

[Anonymous · Accepted solution]

What you are trying to achieve shouldn't be too complicated.

I took the sample you provide and came up with 2 measures. You can paste the first measure in the second measure if you rather have it all together.

Measure 1:

Measure =
CALCULATE (
    SUM ( FTEDetail[Time] );
    MONTH ( FTEDetail[Month] ) = MONTH ( TODAY () )
)

Measure 2:

Measure 2 =
SWITCH (
TRUE ();
[Measure] = 1; "Complete";
[Measure] > 1; "Overtime";
[Measure] < 1; "Incomplete";
"Missing"
)

If you have any questions don't hesitate to ask.

Kind regards
Joren Venema

Data & Analytics Consulant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily.