12-09-2018 01:43 PM
I have a matrix report built in SSRS that I'm trying to port to Power BI. It reports 12 months (columns) of a metric for 10 categories (rows). That is pretty easy to replicate within PowerBI. However I then need to take the average, standard deviation, and Coefficient for each row.
12-09-2018 02:12 PM
Can you share the sample of data what you are having and the expected result.
In power bi standard deviation can be calculated using the STDEV functions.
See the information on the https://docs.microsoft.com/en-us/dax/stdev-s-function-dax
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
12-09-2018 05:53 PM
Thanks Affan, here is an example of the report I need to produce. The data comes in via MDX from an SSAS cube with All, Top 10 N, and All Other calculated by the MDX code. The code also selects the last 12 months. The data is cross tabbed Category by Month. The blue values arrive in the data, I am seeking to calculate the green shaded values. Ultimately I would like to use conditional formatting for values that are more than 1 standard deviation from the average. This can be done pretty easily in SSRS. I have about 10 different parameters/slicers which make this much more appropriate for PowerBI if there is a way of presenting a matrix and/or a table to handle this like the matrix report in SSRS can.
12-10-2018 09:30 PM
If I understand your requirement correctly that you want to achieve the green shade values.
For your scenario, you could calculate the average, you could create the calculated column with the formula below.
average = CALCULATE ( AVERAGE ( 'Table1'[Value] ), ALLEXCEPT ( Table1, Table1[Cate] ) )
To calculate the Standard Deviation, you could refer to the suggestion of Affan to use the fucntion STDEV functions.
However, if you want to achieve the matrix in power bi like the image you share, I'm afraid that we may not achieve that matrix directly. You should create two matrix and then do some modifies.
More details about this workaround, you could refer to my attachment.
12-24-2018 12:52 AM
Have you solved your problem?
If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please feel free to ask.