Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello All,
In my table i have data of employees intime and outtime .
By using those columns i have calculated the time spend using below calculated column formula.
_SpendHrs = DATEDIFF('Table'[InTIME],'Table'[OutTIME],MINUTE)/60
Now i am trying to calculate the average of this spendhrs.
*EmpName are intentionally coloured as white.
for _SpendHrs column i choosed average(in image).
Here Im filtering it out by begin_date column and employee column which are from same table columns.
Now for each date range as it gets changes the average also changing.
I want to categorise each employee by their averages like
<5, 5-7, 7-9, 9-11, >11.(above image its 7.69 -->7-9)
I have written below measure
AvgSpentHrs = CALCULATE(AVERAGE('Table'[_SpendHrs]),ALLSELECTED('Table'[begin_date]))
And the output is as i need,
Then i have written a switch function conditional calculated column to categories these averages and for the above date range and for that employee i expected that it should get in "7-8 hrs".
_SpentHrsDuration = SWITCH(TRUE(), [AvgSpentHrs]<=5,"<5 Hrs", [AvgSpentHrs]>5 && [AvgSpentHrs] <=7,"5-7 Hrs", [AvgSpentHrs]>7 && [AvgSpentHrs]<=8,"7-8 Hrs", [AvgSpentHrs]>8 && [AvgSpentHrs] <=9,"8-9 Hrs", [AvgSpentHrs]>9 && [AvgSpentHrs] <=10,"9-10 Hrs", [AvgSpentHrs]>10 && [AvgSpentHrs] <=11,"10-11 Hrs", [AvgSpentHrs] >11,"Greater Than 11 Hrs")
But when i included that column in table visual the average values are changed.
Can someone please help me with this.
I will be soo thankful you.
Mohan V.
Hey,
the problem you are facing is the following:
You can't access measures from calculated columns, measures are created "on top" of an existing data model and for this reason are able to reflect user interaction. Calculated Columns are one part that "form" the data model.
For this reaon you may have to consider the following, use a measure to create the bin (the bucket the employee belongs to) or create an unrelated table that contains the various elements.
I guess you need to create some sample data, upload the pbix file to onedrive or dropbox and share the link.
Regards
Tom
@TomMartens thanks for the reply.
Here is the sample pbix file with sample data. please have a look.
https://1drv.ms/u/s!AhiQ2f7YQHC-gbNTnJsb9c4LX_uJ5Q
let me know if you need anything.
thanks.
Mohan V
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |