cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nbarta
Frequent Visitor

How to calculate count of IDs per Average Number of Hours bucket

I am very new to PBI and DAX in general so I'm sorry if this is obvious but I would really appreciate any detailed explanations. I have a table with PersonID and Average Number of Hours per Week in a table that looks like this: 

PersonIDAverage Number of Hours per Week
10040
20135
30232
44340
51240
64332

 

and I would like to make a table/graph to show this data 

 

Average Number of Hours Per WeekCount of PersonID
403
351
322
1 ACCEPTED SOLUTION

Accepted Solutions
nbarta
Frequent Visitor

Re: How to calculate count of IDs per Average Number of Hours bucket

Thanks @dm-p I think I managed to find myself to a solution. I ended up exporting the data from the measure I made which did result in a new table with a 1:1 relationship between ID and average hours worked, each of the values becoming implicit measures I believe. I played around enough to get the chart accurate, although there are a few outliers that proved to be true because of weird, but accurate data. Thanks for taking the time to help me out! This community seems really nice2.PNG

View solution in original post

5 REPLIES 5
Super User I
Super User I

Re: How to calculate count of IDs per Average Number of Hours bucket

Hi @nbarta,

Welcome to learning Power BI and DAX!

I assume you're getting something like this when you try?

image.png

If so, Power BI is treating your Average Number of Hours per Week field as an implicit measure, because it's numeric.

You don't want to change this behaviour, as this is a good candidate for other calculations, but you can change this for the visual that you want to group it in.

In the fields list, click on the caret (down arrow) next to Average Number of Hours per Week and select Don't summarize, e.g.:

image.png

This will tell the visual to group by the unique values rather than aggregate them, and this should give you what you need, e.g.:

image.png

Hopefully this should be all you need to proceed.

Just to let you know, if you have any further questions specific to DAX, then it's generally better to post them in the DAX Commands and Tips forum, as it is a lot more heavily frequented than this developer forum and you'll get a much faster answer. The DAX folks don't look in here too often 😉

All the best,

Daniel


If my post solves your challenge, then please consider accepting as a solution to help other forum members find the answer more quickly 🙂





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

Proud to be a Super User!




Highlighted
nbarta
Frequent Visitor

Re: How to calculate count of IDs per Average Number of Hours bucket

Hi @dm-p  Thank you for replying! I'll take that advice and start looking in the other forum more than this one 🙂 

 

Your suggestion did not quite work for me. I think the issue is that my Average Hours by Week also is a calculated measure: 

 

APW2 =
AVERAGEX(
    KEEPFILTERS(VALUES('Table'[Date_wkOfYrNbr])),
    CALCULATE(SUM('Table'[M_Proj_Total_Hrs]))
)
 
When I select the down arrow for average hours, I don't see the same screen you are 
 
 
1.PNG
Super User I
Super User I

Re: How to calculate count of IDs per Average Number of Hours bucket

Hi @nbarta,

Thanks for coming back. It's good to know whether you're working with a measure or a column - I assumed a column. You won't get the Don't summarize option with a measure as measures are always aggregations and can't work as a grouping like you want in this case.

The challenge you'll have here is that the measure is going to perform as an aggregation of the values for the current row context - without a suitable accompanying column/field in the visual to group by, you're likely to always get a scalar (single) value because there are no rows in the resulting dataset.

The fact that you're using KEEPFILTERS implies that you're wanting this measure to be dynamic based on filter context?

What you want is quite an advanced scenario (at least for me!) and I think it needs a wider audience for input. I would suggest cross-posting this question in the previously-linked DAX forum with as much info as possible and I'm optimistic they can help you  much faster than if the question remains open here.

Good luck!

Daniel





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

Proud to be a Super User!




nbarta
Frequent Visitor

Re: How to calculate count of IDs per Average Number of Hours bucket

Thanks @dm-p I think I managed to find myself to a solution. I ended up exporting the data from the measure I made which did result in a new table with a 1:1 relationship between ID and average hours worked, each of the values becoming implicit measures I believe. I played around enough to get the chart accurate, although there are a few outliers that proved to be true because of weird, but accurate data. Thanks for taking the time to help me out! This community seems really nice2.PNG

View solution in original post

Super User I
Super User I

Re: How to calculate count of IDs per Average Number of Hours bucket

Hi @nbarta, and it's great that you found an approach that works for you! It might be worth marking your previous post as the solution, just to close-off the thread and allow any followers to see where the resolution ended up.

Glad you're finding the community a nice place to be - it's a great place to learn and network 🙂

All the best,

Daniel





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

Proud to be a Super User!




Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.