Total of average column in Matrix is incorrect

[Jqiu]

I'm a very beginner Powerbi user so I'm trying to create a table just using all the drag and drop features. However the total is wrong for columns that is the Average of something. Is there a way to only total the a, b, Revenue and Purchases columns? Or is there a way to correctly sum the total for the Avg columns?

[v-xicai · Accepted solution]

Hi Jqiu,

For your question1,You can create measure "Avg" to implement only total special columns. The ISINSCOPE function returns true when the specified column is the level in a hierarchy of levels. The measure display result showing below picture.

Avg = IF(ISINSCOPE([a]),AVERAGE([Column]),BLANK())

For your question2 ,this looks like a measure totals problem that total row data join in calculation for three Avg columns. Essentially, create three measures "Calculate_Avg1", "Calculate_Avg2", "Calculate_Avg3" that calculates correct result at the row level.

Calculate_Avg1= AVERAGE(Column1)

Calculate_Avg2= AVERAGE(Column2)

Calculate_Avg3= AVERAGE(Column3)

Then, create three measures "Total_Avg1" ,"Total_Avg1" ,"Total_Avg1" that performs a SUMMARIZE of your data, exactly as how it is displayed in your table, and use the measures within that SUMMARIZE function to provide the values for the individually summarized rows. Finally, perform a SUMX function across that summarized table to employed in the Total line. The HASONEVALUE function check whether it is really necessary to use that the SUMX. Assume the Column data calculated for average is from the table TableA.

Total_Avg1=

VAR _table = SUMMARIZE('TableA',[a],[b],[Revenue],[Purchases],[Conversion Rate],"_Value1",[Calculate_Avg1])

RETURN

IF(HASONEVALUE([a]),[Calculate_Avg1],SUMX(_table,[_Value1]))

Total_Avg2=

VAR _table = SUMMARIZE('TableA',[a],[b],[Revenue],[Purchases],[Conversion Rate],"_Value2",[Calculate_Avg2])

RETURN

IF(HASONEVALUE([a]),[Calculate_Avg2],SUMX(_table,[_Value2]))

Total_Avg3=

VAR _table = SUMMARIZE('TableA',[a],[b],[Revenue],[Purchases],[Conversion Rate],"_Value3",[Calculate_Avg3])

RETURN

IF(HASONEVALUE([a]),[Calculate_Avg3],SUMX(_table,[_Value3]))

You can refer to this post about similar case: https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907 .

If you have any other issue, please feel free to ask.

Best Regards,

Amy