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.
Hi,
I have a large dataset linked into powerbi which is currently creating me great dashboards.
However, is it possible for PowerBi to give me Z scores on certain variables based on others:
Example - Z-Score for Person A's total steps for today compared to a normal Thursday.
I do not have the Z values in the raw dataset - im looking for PowerBi to create it itself and then plot it in my charts.
Thanks
Solved! Go to Solution.
Based on my reserach, there is a function to return Z-Score directly. We need to calculated in DAX.
Mean = CALCULATE(AVERAGE(Table1[Steps]),ALLEXCEPT(Table1,Table1[WeekDayName]))
StandardDeviation = CALCULATE(STDEV.P(Table1[Steps]),ALLEXCEPT(Table1,Table1[WeekDayName]))
Z-Score column = (Table1[Steps]-Table1[Mean])/Table1[StandardDeviation]
measure
Z-Score measure =
var mean = CALCULATE(AVERAGE(Table1[Steps]),ALLEXCEPT(Table1,Table1[WeekDayName]))
var StandardDeviation = CALCULATE(STDEV.P(Table1[Steps]),ALLEXCEPT(Table1,Table1[WeekDayName]))
return (SUM(Table1[Steps])-mean)/StandardDeviation
Regards,
Charlie Liao
Hi,
I have encountered a similar challenge.
I would like to calculate z scores that would be responsive to several filters/slicers in the report view, which come from different tables.
Model view:
Link to data source:
The correct example below that I would like to get (please note for z score I use stdev.S function when calculating standard deviation):
The incorrect values I get in Power BI:
Some code examples I've tried:
1)
Based on my reserach, there is a function to return Z-Score directly. We need to calculated in DAX.
Mean = CALCULATE(AVERAGE(Table1[Steps]),ALLEXCEPT(Table1,Table1[WeekDayName]))
StandardDeviation = CALCULATE(STDEV.P(Table1[Steps]),ALLEXCEPT(Table1,Table1[WeekDayName]))
Z-Score column = (Table1[Steps]-Table1[Mean])/Table1[StandardDeviation]
measure
Z-Score measure =
var mean = CALCULATE(AVERAGE(Table1[Steps]),ALLEXCEPT(Table1,Table1[WeekDayName]))
var StandardDeviation = CALCULATE(STDEV.P(Table1[Steps]),ALLEXCEPT(Table1,Table1[WeekDayName]))
return (SUM(Table1[Steps])-mean)/StandardDeviation
Regards,
Charlie Liao
Hi, i'm tryin this, buy i have a error, can you help me?
Why do you include the allexept statement? What does that do? Do you need that when you look at it for an entire population without any comparison to determine the z-score for each item?
thanks a lot, Charlie
you really helped me.
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |