cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
keobrie Frequent Visitor
Frequent Visitor

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

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?
4 REPLIES 4
affan Established Member
Established Member

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

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 Frequent Visitor
Frequent Visitor

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

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.

 

 Capture.PNG

Highlighted
Community Support Team
Community Support Team

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

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.

 

Capture.PNG

 

More details about this workaround, you could refer to my attachment.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

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

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.