How to get average based off multiple columns?

[nick-evans]

I have a column in my data that holds a unique value called CaseNumber.

I have another column that lists an OwnerID.

A single OwnerID may be associated with multiple Cases, but a Case can only have one OnwerID.

Here is my Table of data:

I am building a graph to display the average number of cases that occur within a given time frame.

Currently, I can add Count(Distinct) for CaseNumber, and it will tell me how many cases occurred in a given time.

I can add Count(Distinct) for my OwnerID, and it will also show me how many Owners were in that same time.

This effectively means, that the total number of shown cases in a period, were divided amongst the total number of shown Owners.

In my data, however, not every Owner has the same number of Cases.

This is also shown in the above graph as I outlined.

For my purposes, I need to demonstrate what the average number of cases in a period is, based on the Total Distinct CaseNumbers/Total Distinct OwnerIDs.

This would mean, for example, that in August, even though 'Jim' owned 10/11 cases, and 'Tom' owned 1/11, I want the data to represent 5.5 (11 cases /2 owners = 5.5 cases each).

Additionally, the table is just an example. 

I have thousands of records that span many years and have varying categories - in case that impacts possible answers.

I have been pouring through different threads about possibilities done through a new Column, concatenation of a pair of values, I just am not seeing what I need though.

Also, if there's a better description for what I'm trying to do (i.e. better than 'How to get average based off multiple columns') please let me know - this exact title is used on at least one other thread and that thread is something different from what I want, so I figure maybe there's a better way to phrase what I'm after here?

[BILASolution · Accepted solution]

Hi @nick-evans

I hope it helps

Count Distinct Owners = DISTINCTCOUNT(Table1[OwnerId])

Total Cases = COUNT(Table1[CaseNumber])

Average = DIVIDE([Total Cases];[Count Distinct Owners])

[nick-evans]

@BILASolution - 

Thanks for the reply.

Your screenshot definitely looks like you were able to get to the type of outcome that I am after.

I tried incorporating your calculations into new columns, but when I setup my table as you show yours, my calculations are crazy now.

The default aggregation for Values appears to be 'Sum', so now that 'Total Cases' has set a value of '19', my Total Cases in the Graph shot up to 209 (should be 19).

Did you change any of your aggregations? 

Unfortunately, the logic of the formulas still is confusing me enough that I'm having a hard time intuiting what else might need to be tweaked here to make your suggestion work as you show it does in your screenshot.

[BILASolution]

@nick-evans

Try to create Count Distinct Owners, Total Cases and Average as Measures instead Calculated Columns.