Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a visual showing the workload of employees per calendar week and it worked quite nice with below formulas.
Workload = Plan[Sum_ActualWorkTime]/Employee[Sum_PayedWorkTime]
PayedWorkTime = SUM(Employee[WorkingHours])/6*Plan[WorkDayCount]
Now the problem is that there’s a new employee and they want me to show the reduction in workload that this new guy brings to the department.
Previously: 5 employees with total of 197.5 working hours per week
Now: 6 employees with total of 237.5 working hours per week
I therefore need to break the established visual into two conditions – before and after the new hire. So how can I tell Power BI to calculate the Workload somehow like that:
Workload = IF “Date” < 01.04.2020 Then
Plan[Sum_ActualWorkTime]/Employee[Sum_PayedWorkTime_OLD] Else
Plan[Sum_ActualWorkTime]/Employee[Sum_PayedWorkTime_NEW]
My problem is that I can’t reference the “Date_Column” in a measure and when creating a new column I get a circular dependency error!
Solved! Go to Solution.
Just specify an aggregation such as max to get a single result.
https://dax.guide/selectedvalue/
Hey v-chuncz-msft, that is exactly what I meanwhile found myself to work perfectly.
Just for reasons of completeness the result looks like that:
WorkingHours = IF(MAX(Plan[DATE])<MAX(Employee[HireDate]);
CALCULATE(SUM(Employee[WorkingHours];
FILTER(Employee;Employee[Employee]<>"NewHireName"));
CALCULATE(SUM(Employee[WorkingHours]))
EmployeeCount = IF(MAX(Plan[DATE])<MAX(Employee[HireDate]);
CALCULATE(DISTINCTCOUNT(Employee[Employee]);
FILTER(Employee;Employee[Employee]<>"NewHireName"));
DISTINCTCOUNT(Employee[Employee]))
WorkDayDount = CALCULATE (
DISTINCTCOUNT(Plan[Date]);
FILTER ('Plan';'Plan'[Weekday] <= 5))
PayedWorkTime = Employee[WorkingHours] / Employee[EmployeeCount] * Plan[WorkDayDount]
Thanks everyone for supporting me!
I could introduce a "hire date" to make a distinct count based on that. Is that what you mean? Will give it a try and get back here than.
@MFelix Your formula looks good and I'd like to try it but two things
1. Power BI tells me that SELECTED is invalid - is there something missing or do I do something wrong?
2. I can't use "Table[DATE]" in a measure - how can I get round this?
Thanks for your feedback! Highly appreciated.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHey v-chuncz-msft, that is exactly what I meanwhile found myself to work perfectly.
Just for reasons of completeness the result looks like that:
WorkingHours = IF(MAX(Plan[DATE])<MAX(Employee[HireDate]);
CALCULATE(SUM(Employee[WorkingHours];
FILTER(Employee;Employee[Employee]<>"NewHireName"));
CALCULATE(SUM(Employee[WorkingHours]))
EmployeeCount = IF(MAX(Plan[DATE])<MAX(Employee[HireDate]);
CALCULATE(DISTINCTCOUNT(Employee[Employee]);
FILTER(Employee;Employee[Employee]<>"NewHireName"));
DISTINCTCOUNT(Employee[Employee]))
WorkDayDount = CALCULATE (
DISTINCTCOUNT(Plan[Date]);
FILTER ('Plan';'Plan'[Weekday] <= 5))
PayedWorkTime = Employee[WorkingHours] / Employee[EmployeeCount] * Plan[WorkDayDount]
Thanks everyone for supporting me!
Just specify an aggregation such as max to get a single result.
https://dax.guide/selectedvalue/
Hi @flex99 ,
You can try something similar to:
Workload =
SWITCH(
SELECTED(Table[DATE]) < DATE(2020; 4 ; 1);
Plan[Sum_ActualWorkTime]/Employee[Sum_PayedWorkTime_OLD];
Plan[Sum_ActualWorkTime]/Employee[Sum_PayedWorkTime_NEW])
But be aware that if you want to calculate values overtime you will need to use an aggregator formula as SUMX.
Believe that the best optionm would be to make a distintctcount of the users on a given time and divide the workload by that instead of hardcoding the number of employees.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @flex99 ,
Can you please share some sample data, so that I can suggest the required DAX?
Thanks,
Pragati
User | Count |
---|---|
77 | |
74 | |
42 | |
32 | |
28 |
User | Count |
---|---|
100 | |
93 | |
52 | |
50 | |
48 |