Forum Discussion

SO's avatar
SO
Helper III
4 years ago
Solved

Standard Deviation and Z-Score with Multiple and In-Service

I have a challenge similar to the one found in a similar thread (https://community.powerbi.com/t5/Desktop/Z-Score-Calculation-with-Multiple-Filters/m-p/749498#M361273), but with a few twists.  My data is about a group of people and comes live from in-service.

I want to show the z scores of the courses based on the %s of people assigned an R.    Below each bar represents a course and their Z score based on the percentage of "R"s in a course.  



I have created some sample data that shows the idea working in excel/google.  
The data I access is 'In Service' so I can't create the same approach and so I need to work in Dax and Measures.  

 

Table: People

Course   section  n(all)     Rtot   %ofR      $Mean    $SDV        Deviation      Z- Value

ABC        01           3          1       33.3%                       

ABX        02           113     12      10.6%                      

BBB        01           347      31      8.9%                        

BBB        08            57       5        8.8%                        

CCC        1           1212               0.0%                        

DDD       12         222                 0.0%                        


This table contains thousands of entries and most have no Rs, so there are a lot of blanks or '0's in the data.  People in the courses have been assigned a group and number e.g., R(0-49), L1(50-59), L2 (60-69), L3(70-79), L4(80-100).   

 

**this is an update, I realize now that I need to weigh the Standard deviations.  The reason for this is to recognize that while ABC has 33% Rs, and ABX has 10%, The ABC course only had 3 people, while ABC has a 113.   I think this is the overall formula:  

 

 

n(all) is the count of people in a course (ignoring the section)

 

Rtot is the total number of people assigned to R (anyone with a 0-49 number).  

aka CALCULATE(COUNTA('People'[Group]), 'People'[Group] IN { "R" })

 

%ofR is the percentage of Rs (aka R total divided by the sum of L1, L2, L3, L4). 

Rtot / COUNTA('People'[Group])


Using the code from the other forum I tried to calculate the mean, Standard Deviation and Z-Code.  

 
var _mean = CALCULATE(AVERAGE('People'[group]),Filter(ALLEXCEPT('People',''People'[group]), 'People'[group] >0))
return _mean


I suspect the issue is with the >0 as my values are not numbers. 

I was also not able to use the following to calculate the deviation.  

  • var deviation = CALCULATE(STDEV.P('People'[group]), FILTER( ALLEXCEPT('People',''People'[group]), 'People'[group]>0 ))

I thought that I was able to calculate Standard Deviation via, but it doesn't appear correct.  People are grouped into courses and and sections, but I want to ingore the section and look at the distribution of assigned groups. 

  • $SDV  = Var __Count = CALCULATE(STDEVX.P(People,[#z Decimal % of R])) RETURN __COUNT
  • #z Decimal % of R = 

VAR __BASELINE_VALUE =

    CALCULATE( COUNTA('People'[Group]), 'People'[Group] IN { "R" } )
VAR __MEASURE_VALUE = COUNTA('People'[Group])
RETURN
    IF( NOT ISBLANK(__MEASURE_VALUE), 1 - DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __MEASURE_VALUE))

        

Since there are many coures without a "R"  (Blank or Zeros) any calculations will be skewed, so I'm also trying to find a way to calculate when there is more than 1 person in each group assigned to Group  'R' .  Ideally, when there are more than 1, 2 (insert value) people are assigned an 'R'.    

This is just a few of the challenges related to this as I've spent dozens of hours struggling to work through this.  Any help is much appreciated.   Ultimately, I want to show the z scores of the courses based on the %s of people assigned an R. 

Support is much appreciated!!!  
 

 

 

Need Help

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    SO The solution has been attached. See if this works for you. There are two pages, tweak the formulas as you see fit.

     

    ✨ 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.⚡

  • SO's avatar
    SO
    Helper III
    4 years ago

    To anyone that looked, I think I partially solved this (in a less than ideal way) and will upload Dax once I can verify it is working for various scenarios.  I can find the Z score of the courses, but now I realize that I need to weight them according to the size of the course.  Ugg...

  • SO's avatar
    SO
    Helper III
    4 years ago

    Thank YOU!  parry2k   - this is fantastic!!!  I am confident that when someone goes looking for information on Standard Deviation and Z-Score, this will be the source.  Your Dax measures are clear and crisp!!  I will start to work on creating weighted measures now, but I'm off to such a great start.  Your logic is so clear.  I was trying to bring everything into 3 measures.  This is so helpful!!

    Big Big Kudos to you.  Thanks again for being such a great support to this community !  😁

    Shawn