DAX counting average times a value appears

[Anonymous]

Hi there,

I'm trying to calculate the average number of times a value appears in a column compared to another column.

So, column 1 would be the person's name, column 2 would be the status. As you can see, the status and person's name can appear multiple times. Essentially I want to be able to take the average # of (whatever value) for each person - average # of completed for user AB.

Name	Status
AB	Registered
DC	Started
AB	Completed
XY	Started
OP	Started
OP	Started
OP	Registered
AB	Completed
OP	Completed
XY	Registered
DC	Completed
XY	Started
AB	Completed

[guibenassi]

 

Count Complete =

Calculate(

COUNT(Table[Status])

;Table[Status]="Completed")

The measure will show the count of status using the condition that Status = Completed

Then create a table and choose to show number of % of the total.

[sturlaws]

Hi @Anonymous,

doing an average of the sample dataset you have provided does not make any sense.

Assuming you also have information about date, and want to compute the average number of Completed-occurrences pr month, you code use something like this:

Average number of Completed-occurrences pr month =
AVERAGEX (
    VALUES ( Table[Month] );
    COUNT ( Table[Status] )
)

Or do you want to compute the percentage of completed vs the total number of statuses? In that case use this kind of measure

Percentage of completed =
DIVIDE (
    COUNT ( Table[Status] );
    CALCULATE ( COUNT ( Table[Status] ); ALL ( Table[Status] ) )
)

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[sturlaws]

ah, in that case:

Average number of Completed-occurrences pr month =
AVERAGEX (
    VALUES ( Table[Users] );
    COUNT ( Table[Status] )
)

[Anonymous]

Thanks, @sturlaws . I'm not sure if either of those are what I'm getting at.

The end goal is being able to view the average completions (or other status) per user. So I'd need to be able to count how many times a certain status appears for each user (how many times does Completed show for user AB), then take the average of that among all users.