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 table that has a Metric Name column that consists multiple metrics and a Metric Value column. I want to calculate the z score for the data within each of the AAA, BBB, and CCC metric. As I actually have hundreds of metric to deal with in reality, I wonder if I can calculate the z score in one single column and then use a filter to look at one single metric when I built the dashboard? Or is there a better way to do it in Power BI? I guess I could also pivot the table to get three seperate columns for AAA, BBB, and CCC, but then I'll have to write the same formula three times, too tedious. Any advice? Thanks.
Metric Name | Metirc Value |
AAA | 1.52 |
AAA | 1.90 |
AAA | 0.98 |
AAA | 1.95 |
AAA | 9.38 |
AAA | 1.68 |
AAA | 4.98 |
AAA | 8.33 |
AAA | 3.29 |
AAA | 9.98 |
BBB | 9.71 |
BBB | 6.52 |
BBB | 9.60 |
BBB | 8.17 |
BBB | 9.89 |
BBB | 4.62 |
BBB | 6.77 |
BBB | 1.38 |
BBB | 5.31 |
BBB | 5.57 |
CCC | 8.75 |
CCC | 2.17 |
CCC | 4.00 |
CCC | 2.74 |
CCC | 9.25 |
CCC | 2.71 |
CCC | 6.99 |
CCC | 9.60 |
CCC | 0.89 |
CCC | 6.94 |
Solved! Go to Solution.
Hi,@wsspglobal,
yes, you can:
z score = var _metric = CALCULATE(SELECTEDVALUE('Table'[Metric Name])) var _sampleMean = CALCULATE(AVERAGE('Table'[Metric Value]);FILTER(ALL('Table');'Table'[Metric Name]=_metric)) var _sampleSD = CALCULATE(STDEV.S('Table'[Metric Value]);FILTER(ALL('Table');'Table'[Metric Name]=_metric)) return DIVIDE('Table'[Metric Value]-_sampleMean;_sampleSD)
Cheers,
Sturla
Hi @wsspglobal ,
You can create column or measure like DAX below.
Column1= CALCULATE(SUM(Table1[Metric Value]),FILTER(ALLSELECTED(Table1), Table1[Metric Name] =EARLIER(Table1[Metric Name]))) Measure1= CALCULATE(SUM(Table1[Metric Value]),FILTER(ALLSELECTED(Table1),Table1[Metric Name]=MAX(Table1[Metric Name])))
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,@wsspglobal,
yes, you can:
z score = var _metric = CALCULATE(SELECTEDVALUE('Table'[Metric Name])) var _sampleMean = CALCULATE(AVERAGE('Table'[Metric Value]);FILTER(ALL('Table');'Table'[Metric Name]=_metric)) var _sampleSD = CALCULATE(STDEV.S('Table'[Metric Value]);FILTER(ALL('Table');'Table'[Metric Name]=_metric)) return DIVIDE('Table'[Metric Value]-_sampleMean;_sampleSD)
Cheers,
Sturla
Hi @sturlaws
I can't type the last part:
DIVIDE('Table'[Metric Value]-_sampleMean;_sampleSD)
It seems like I cannot call out a column within DIVIDE. When I tried to call 'Table'[Metric Value], I can't. Please advise. Thank you!
Sorry, I forgot to mention that this would be the code for a calculated column. Are you trying to do it as a measure?
z score measure = VAR _metric = CALCULATE ( SELECTEDVALUE ( 'Table'[Metric Name] ) ) VAR _sampleMean = CALCULATE ( AVERAGE ( 'Table'[Metric Value] ); FILTER ( ALL ( 'Table' ); 'Table'[Metric Name] = _metric ) ) VAR _sampleSD = CALCULATE ( STDEV.S ( 'Table'[Metric Value] ); FILTER ( ALL ( 'Table' ); 'Table'[Metric Name] = _metric ) ) RETURN DIVIDE ( SUMX ( VALUES ( 'Table'[Metric Value] ); SUM ( 'Table'[Metric Value] ) ) - _sampleMean; _sampleSD )
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |