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
sieed
Helper II
Helper II

Calculate average of values per date

I have a table below: 

bb.PNG

I am trying to get the average of "someNumber" (which is number of seconds)  per date and then convert that number into miutes and seconds. 

So, I am drawing the graph below: 

some.PNG

As you can see, I used the average filter and it calculated the average of "someNumber" per date. For instance, the average on 1st is 13. Now, how do I convert 13  into minutes and seconds so that I can display it as a tooltip? 

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
Employee

Hi @sieed,

I try to reproduce your scenario and get expected result.

In report interface, the hierarchy of date is to day level rather than hour, minute, and second. You'd better create measure to calculate the average of number on minute and second.

Minute = CALCULATE(SUM(Test[SomeNumber]),ALLEXCEPT(Test,Test[date].[Day]))/DATEDIFF(CALCULATE(MIN(Test[date]),ALLEXCEPT(Test,Test[date].[Day])),CALCULATE(MAX(Test[date]),ALLEXCEPT(Test,Test[date].[Day])),MINUTE)

 

Second = CALCULATE(SUM(Test[SomeNumber]),ALLEXCEPT(Test,Test[date].[Day]))/DATEDIFF(CALCULATE(MIN(Test[date]),ALLEXCEPT(Test,Test[date].[Day])),CALCULATE(MAX(Test[date]),ALLEXCEPT(Test,Test[date].[Day])),SECOND)

Then create a line chart to display the result.

1.PNG
The result is little, so you see the two line is closest to coinciding. Actually, they are different.

Best Regards,
Angelia

View solution in original post

1 REPLY 1
v-huizhn-msft
Employee
Employee

Hi @sieed,

I try to reproduce your scenario and get expected result.

In report interface, the hierarchy of date is to day level rather than hour, minute, and second. You'd better create measure to calculate the average of number on minute and second.

Minute = CALCULATE(SUM(Test[SomeNumber]),ALLEXCEPT(Test,Test[date].[Day]))/DATEDIFF(CALCULATE(MIN(Test[date]),ALLEXCEPT(Test,Test[date].[Day])),CALCULATE(MAX(Test[date]),ALLEXCEPT(Test,Test[date].[Day])),MINUTE)

 

Second = CALCULATE(SUM(Test[SomeNumber]),ALLEXCEPT(Test,Test[date].[Day]))/DATEDIFF(CALCULATE(MIN(Test[date]),ALLEXCEPT(Test,Test[date].[Day])),CALCULATE(MAX(Test[date]),ALLEXCEPT(Test,Test[date].[Day])),SECOND)

Then create a line chart to display the result.

1.PNG
The result is little, so you see the two line is closest to coinciding. Actually, they are different.

Best Regards,
Angelia

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.