Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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#M3612...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.
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.
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.
VAR __BASELINE_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'.
Solved! Go to Solution.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |