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

[nbarta]

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: 

PersonID	Average Number of Hours per Week
100	40
201	35
302	32
443	40
512	40
643	32

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

Average Number of Hours Per Week	Count of PersonID
40	3
35	1
32	2

[dm-p]

Hi @nbarta,

Welcome to learning Power BI and DAX!

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

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.:

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.:

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 🙂

[nbarta]

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 

[dm-p]

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

[nbarta · Accepted solution]

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 nice

[dm-p]

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