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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Bu__
Frequent Visitor

Average of time spent by states in a month

I have a data like this,

Month - YearSum of Time spentCount ofIDAvg. Time spent in Hrs States
Oct-2355.6124.64Active (wip)
Oct-2372.85126.08Assigned
Oct-232.5270.36New
Oct-230.320.15Pending - 3rd Party
Oct-23136.58915.18Pending - End User
Oct-230.1530.05Rejected - Not responsible
Oct-230.18100.02Resolved - Solved for CU
Oct-230.640.15Resolved - SP part finished
Total268.781222.42 

Here the avg time spent in hrs is not totalling properly

It's becasue the Distinct count of ID in the month is 12 but here due to states being repeated in every id , count of ID for each state is showing the number  of times it is appearing when it should be only 12

HEre i have used a dax measure as shown below to calculate average time spent by states in months

ts_avg_month =
CALCULATE(
AVERAGEX (
SUMMARIZE ('Table',
' date'[date] ,
'Table'[ID],
"Monthly Time spent per ID",
SUM ( 'Table'[Time spent] )
),
[Monthly Time spent per id]
)
) so how do i correct this so that i get the correct averages for each states?

Thanks in advance

1 ACCEPTED SOLUTION
v-jiewu-msft
Community Support
Community Support

Hi @Bu__ ,

Please try the following methods and check if they can solve your problem:

1.Create the simple table.

vjiewumsft_0-1709172081188.png

2.Create the new measure to calculate average.

 

st_avg_month = 
VAR Dis_ID = DISTINCTCOUNT('Table'[ID])
RETURN
    DIVIDE(
        SUM('Table'[Time spent]), 
        Dis_ID
    )

 

 

3.Drag the measure into the visual. The result is shown below.

vjiewumsft_1-1709172113877.png

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

 

View solution in original post

2 REPLIES 2
v-jiewu-msft
Community Support
Community Support

Hi @Bu__ ,

Please try the following methods and check if they can solve your problem:

1.Create the simple table.

vjiewumsft_0-1709172081188.png

2.Create the new measure to calculate average.

 

st_avg_month = 
VAR Dis_ID = DISTINCTCOUNT('Table'[ID])
RETURN
    DIVIDE(
        SUM('Table'[Time spent]), 
        Dis_ID
    )

 

 

3.Drag the measure into the visual. The result is shown below.

vjiewumsft_1-1709172113877.png

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

 

some_bih
Super User
Super User

Hi @Bu__ 

It seems that your you did not include State in your SUMMARIZE table

Try v2 below

ts_avg_month 2=

CALCULATE(
AVERAGEX (
SUMMARIZE ('Table',
' date'[date] ,
'Table'[ID],
'Table'[States],
"Monthly Time spent per ID",SUM ( 'Table'[Time spent] )
),
[Monthly Time spent per id]
)
)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.