Average of table

[Anonymous]

i have a matrix table as per screenshot with setup

what i would like to achieve is create a column on the right with the average of the 5 columns totals 

Stuck on how to achieve this. tried a few different methods and getting incorrect results. essentially my output should look similar to this for the FY18/19 period

FY18/19	Product1	Product2	Product3	Product4	Product5	Average
Q1	20.31	75.00	311.30	0.00	554.60	192.24
Q2	249.00	0.00	1446.97	148.00	1115.95	591.98
Q3	137.50	15.00	473.40	14.62	575.08	243.12
Q4	420.25	15.25	134.00	94.71	768.70	286.58

thanks

[v-gizhi-msft]

Hi,

According to your description, i create a table to test:

Then create a column to calculate average values for each Q:

Average = CALCULATE(AVERAGE('FY Table'[Values]),ALLEXCEPT('FY Table','FY Table'[Q]))

Add this column to the matrix columns field, it shows:

Then turn off 'Word Wrap' and hide the first four average, and it shows:

Hope this helps.

Best Regards,

Giotto Zhi

[Anonymous]

@v-gizhi-msft 

thanks for the reply

im a bit confused as to why you created a table to display the average or was that to just get the ouput?

possibly i wasnt clear in my original output. the columns are the totals for each product per quarter but then i wish to get the average at the end

[v-gizhi-msft]

Hi,

The average values in my screenshot is the average result from five products for each Q.

Please review my original screenshot and you will find that the average columns for each product are the same.

Best Regards,

Giotto Zhi

[Anonymous]

@v-gizhi-msft 

thanks

ive been still racking my brain as my average output seems to be wrong and im not sure what i am doing wrong

my formula is

Average = CALCULATE(AVERAGE('Service Reliability'[Re-calculated Downtime]),ALLEXCEPT('Service Reliability','Date'[Financial Year],'Date'[FY Quarters]))

my Financial Year and FY Quarters is based on a separate date table. the linked column in my main table is just called "Date"

100% know im doing something wrong with my formula. ive tried multiple different ways after reading blogs and DAX on allexcept

[v-gizhi-msft]

Hi,

Could you please tell me the relationship between your main table and date table? one-to-one or one-to-many or many-to-many?

Also i notice that your formula is a little different from my original formula, please try to change the table in ALLEXCEPT function:

Average = CALCULATE(AVERAGE('Service Reliability'[Re-calculated Downtime]),ALLEXCEPT('Date','Date'[Financial Year],'Date'[FY Quarters]))

Wait for your reply.

Best Regards,

Giotto Zhi

[Anonymous]

@v-gizhi-msft  
the relationship is a many-to-one 

your change was one i did myself previously and was getting different figures for each column, so i stopped going down that path

[v-gizhi-msft · Accepted solution]

Hi,

Is this data in your screenshot the sum value for each product?(Simplely means one product has more than one value for each Year/Q)

If so, i create two new tables to test:

   

Create a sum measure for each product of each Year/Q:

Measure = SUM('Product'[Values])

Then create an average measure for each Year/Q:

Average = CALCULATE(SUM('Product'[Values]),ALLEXCEPT('Date','Date'[Q],'Date'[Financial Year]),ALLSELECTED('Product'[Product]))/3

Choose these two measures and follow my previous reply's steps, it shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[Anonymous]

@v-gizhi-msft 

thats it

fantastic

thankyou so much