cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MohanV Established Member
Established Member

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
Super User
Super User

Re: Average Calculation

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
MohanV Established Member
Established Member

Re: Average Calculation

@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

MohanV Established Member
Established Member

Re: Average Calculation

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

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 242 members 2,641 guests
Please welcome our newest community members: