Distinct count *only* at a certain level, even if other levels are showing

[Zarlot531]

So i have a dataset that has 4 fields for the last 18 months:

Job Type

Job Code 

Part Code 

Part Cost

any given job code can be repreated multiple times because any given job can have multiple and various parts. Not all parts show up on each job though.

What I ultimately want to do is build a pivot table where I can have job type as an overall filter with part code running down the left side (rows) and have an "average" cost per job as a measure. 

BUT.... Power BI wants to only calculate the average of course for when the part codes show up. But... I want overall average meaning total sum of each part divided by total jobs. For what I'm trying to do, Power BI will, for example, if a part only shows up once it'll consider the average that entire cost when realy it should be divided by the number of unique jobs. 

But I'd like to be able to filter the overall pivot by job type as well. In other reports, I've had to resort to writing SQL at the server level to get zeros added for every missing part but god that's so dang efficient. Surely there's a better way. Every time I write a countrows or distinct count though, it just does'nt do what I need it to. THanks

[parry2k]

😃

[parry2k]

@Zarlot531 first and foremost, it is much easier to answer if you share sample data with the expected output otherwise it is hard to reply, Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

2nd, it is a best practice to create a star schema, in your case if you have a separate for each job (work as a dimension) it will be very easy to work with. Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Zarlot531]

Here's the crux of my issue. There are 2,058 unique jobs and I can get to that number using the following formula.

But... when I filter on job type, it doesn't adjust. 

[Zarlot531 · Accepted solution]

Silly me... i had the wrong formula field above. This solves it.