Assign number based on mean/SD with filters

[Anonymous]

I'm looking for the best way to do this...

We have 10 employees, 5 at each location. They're each evaluated on the same 5 metrics.

We want to give them a score of 1-4 for each metric (so they'd have a total possible 20 points) so they can be plotted on a radar graph.

However the metrics aren't apples to apples (some are minutes, some are NPS, some are $$, etc.) 

We'd like to set up where the scores are determined by where they fall relative to the mean and SD. 

IE. if the mean is 100 and SD is 10 and higher is better..

If their score is greater than 110, they get a 4, if it's between 110-100, they get a 3, etc. 

BUT we don't want to hardcode these values in - they should be determined based on the mean/SD of that metric at that location (so if Employee A works at Location 1, their Mean/SD would be from location 1, not all inclusive)

I know how I'd do this in Excel, but I'm newer to DAX. I've tried to figure this one out myself, but I'm stuck, going in circles and on a deadline. Any help is GREATLY appreciated!

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example. Can you share sample data and sample output.

Appreciate your Kudos.

[Anonymous]

Sample file is here

@amitchandak @Greg_Deckler 

Example of how the "score" would be determined for Avg Call Length (greater is better). The mean and SD should be only for their location (North or West) NOT the entire population.

Score	Criteria
1	< mean - SD
2	between mean - SD and mean
3	between mean and mean + SD
4	> mean + SD

[Anonymous]

I have also uploaded an Excel file with the source data as well as how I'd do this in Excel (see column Call Score) here

[Icey]

Hi @Anonymous ,

Please use other tools to share your files. It is suggested to upload your files to OneDrive for Business. Please remove sensitive information.

Best Regards,

Icey

[Anonymous]

Files uploaded to an alternate source, all the data is sample data and not sensitive.

[Icey · Accepted solution]

Hi @Anonymous ,

Sorry to reply late. Please check:

Mean = 
CALCULATE (
    AVERAGE ( Sheet1[Avg Call Length] ),
    ALLEXCEPT ( Sheet1, Sheet1[Site] )
)

SD = 
CALCULATE (
    STDEV.P ( Sheet1[Avg Call Length] ),
    ALLEXCEPT ( Sheet1, Sheet1[Site] )
)

Score = 
VAR Avg_Call =
    SUM( Sheet1[Avg Call Length] )
RETURN
    SWITCH (
        TRUE (),
        Avg_Call < [Mean] - [SD], 4,
        Avg_Call >= [Mean] - [SD]
            && Avg_Call <= [Mean], 3,
        Avg_Call >= [Mean]
            && Avg_Call <= [Mean] + [SD], 2,
        Avg_Call > [Mean] + [SD], 1
    )

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you! This works perfectly for calculating their scores. They each now have a score, 1-4 for each metric.

However - I'd like their score to be what shows up on the radar chart for each metric. I normally would unpivot and have the value for each metric, but I can't do that with the measures here. Any ideas? I tried creating a new table and pulling the measures in as rows, but I can't get their name to create a relationship so it filters.

[Greg_Deckler]

Well, would need sample data for this. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

If you can do it in Excel, look here: https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991

@Anonymous