Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Average Calculation

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.

Capture.JPG

 

Capture.JPG

 

*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,

Capture.JPG

 

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.

Capture.JPG

 

 

Can someone please help me with this.

 

I will be soo thankful you.

 

Mohan V.

3 REPLIES 3
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

@TomMartens could you please give any suggestions on this..

Anonymous
Not applicable

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.