Forum Discussion
calculating standard deviations from Matrix visual
PBIX file is here: https://www.dropbox.com/s/uasonb98zmz41xw/ATV%2C%20growth%20and%20indexing.pbix?dl=0
I have a matrix visual that appears as follows, except for the StdDev column on the right. That column is what I'm trying to calculate and is the desired output - I don't need it in the Matrix visual, but do need the values associated with Low/Mid/High rows, and need it to be dynamic, as the Matrix visual will eventually be filtered by slicers. The StdDev column is the Standard Deviation of the annual % change in the upper table, calculated from the lower table.
Thanks!
| 2008 | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | StdDev | |
| Low | $624 | $990 | $914 | $812 | $926 | $914 | $893 | $926 | $846 | $847 | $762 | 20.8% |
| Mid | $512 | $841 | $771 | $685 | $780 | $779 | $808 | $819 | $751 | $739 | $645 | 22.7% |
| High | $454 | $709 | $670 | $582 | $645 | $651 | $699 | $696 | $628 | $609 | $404 | 23.0% |
| 2008 | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | StdDev | |
| Low | 58.6% | (7.7%) | (11.1%) | 14.0% | (1.3%) | (2.3%) | 3.6% | (8.6%) | 0.2% | (10.1%) | 20.8% | |
| Mid | 64.4% | (8.3%) | (11.2%) | 13.8% | (0.1%) | 3.8% | 1.3% | (8.3%) | (1.6%) | (12.7%) | 22.7% | |
| High | 56.2% | (5.4%) | (13.2%) | 10.9% | 0.8% | 7.4% | (0.4%) | (9.8%) | (3.1%) | (33.7%) | 23.0% |
Hi mrothschild,
Please try this measure.
Measure = VAR a = SELECTCOLUMNS ( 'Data', "value", [Value] ) RETURN STDEVX.S ( SUMMARIZE ( 'Data', 'Calendar'[Year], Data[Attribute] ), IF ( [Annual % change] <= 1, [Annual % change], BLANK () ) )
BTW, DO NOT share anything confidential here.
Best Regards,
2 Replies
Hi mrothschild,
Please try this measure.
Measure = VAR a = SELECTCOLUMNS ( 'Data', "value", [Value] ) RETURN STDEVX.S ( SUMMARIZE ( 'Data', 'Calendar'[Year], Data[Attribute] ), IF ( [Annual % change] <= 1, [Annual % change], BLANK () ) )
BTW, DO NOT share anything confidential here.
Best Regards,
Thank you!
