Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |