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
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
nbarta
Frequent Visitor

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

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!


My course: Introduction to Developing Power BI Visuals


On how to ask a technical question, if you really want an answer (courtesy of SQLBI)




nbarta
Frequent Visitor

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

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!


My course: Introduction to Developing Power BI Visuals


On how to ask a technical question, if you really want an answer (courtesy of SQLBI)




nbarta
Frequent Visitor

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

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!


My course: Introduction to Developing Power BI Visuals


On how to ask a technical question, if you really want an answer (courtesy of SQLBI)




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.