Power BI Table with Average, Standard Deviation, and CV in Grand Totals

[keobrie]

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.

I need Grand Totals for the columns and the average, stdDev, and CV for the Grand Total Row.

Is there any way to do this in Power BI?

[affan]

Hi @keobrie,

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 .

Regards,

Affan

[keobrie]

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.

 

[v-piga-msft]

Hi @keobrie,

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.

Best  Regards,

Cherry

[v-piga-msft]

Hi @keobrie,

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.

Best Regards,

Cherry

[MAAbdullah_47]

Hi @v-piga-msft , I have some questions:

Q1. In the Table below I think there is a problem in calculating the AVG for example in the first line for (C1) it should be 30 Not (150) Is it true or not?

Q2. From business perspective what is the need for calculating standard diviation distance from the mean (average)?

Thank you