Forum Discussion
Helper IColumn Calculations in a Matrix
I have a PowerBI matrix of 6 columns of data...see below. The values are the sums of the last 6 months.
The example Excel worksheet below averages those 6 columns and does other calculations that I need to do in the PowerBI matrix but I can't seem to figure out how to get the average of the columns. The only value that I can get is the grand total of the row but, again, I need average as well as other calcuations.
Thanks
5 Replies
- MattAllington
Community Champion
You have not provided enough information
- mikeoshields
My apologies for not providing enough info. The Excel data below is the result of a pivot. After pivoting, I average the row for the 6 columns (Average Consumption) and do other math. I need to do the same thing in PowerBI
However, when I do a simple AVERAGE('Table'[Column]) in PowerBI in a Matrix, I do not get 23222.5. I get 209.47 and I have no idea why. Is there something extra that I need to in order to average values in a matrix?
- MattAllington
You need to post
* your table structure
* your relationships
if you don't do this, it's like phoning your mechanic and saying "my car won't start, what's wrong"?
- mikeoshields
I know I know...the thing is we're a DOD supplier so I have to be VERY careful. Let's attack this from a different perspective (forget the actual data)...What is the right way to get the average of values in a matrix (see below)? Again, in this case QTY_LOCATION is pivoted into monthly totals. I just need to get the average of the monthly totals in a measure or column (not sure which). If this isn't sufficient, I will make up some data since I can't send my raw data.
- MattAllington
1. get a calendar table https://exceleratorbi.com.au/power-bi-calendar-tables/
2. understand why totals don't add up https://exceleratorbi.com.au/use-sum-vs-sumx/#totals
