Display grand total and average in each row (direct query)

[dp106]

Hi,

I am using direct query. I need the grant total and average to be displayed in each row. is it possible. ? i know i can do this in sql but i dont want to.

Here is an example

Col1   Col2  Sum   Avg

X        100   600    200

Y        200   600    200

Z        300   600    200

Thanks

[v-jiascu-msft]

Hi @dp106,

Could you please mark the proper answer as a solution?

Best Regards,
Dale

[v-jiascu-msft]

Hi @dp106,

You can create two measures like below.

Avg =
CALCULATE ( AVERAGE ( 'table'[Col2] ), ALL ( 'table' ) )

Sum =
CALCULATE ( SUM ( 'table'[Col2] ), ALL ( 'table' ) )

Best Regards,

Dale

[dp106]

Thanks. The solution seems to be working fine but not if i have filters (slicers on the reports).

If i add the mesaure, no matter what filters are applied in the report, all the values are pulled from the database.

Here is how the data is. i have a slicer on the report on col0 (there are couple of filters on the actual report)

Col0 Col1   Col2  Sum   Avg

1 X        100   600    200
1 Y        200   600    200
1 Z        300   600    200

2 X        150   900    300
2 Y        500   900    300
2 Z        250   900    300

Thanks again for looking at it.

[jthomson]

Just need to modify the ALL, which as you'd expect is pulling all the data, to using ALLEXCEPT and specifying Col0 as needed. It will pull through all the values in your database as is, as that's what you're telling it to do...

[dp106]

Thanks for your response.

I tried the solution you recommended, so the calculation works fine but it pulls are the records from the database (ignoring the slicer)

[v-jiascu-msft · Accepted solution]

Hi @dp106,

Just see your data here, please try the formulas below.

Avg = 
CALCULATE ( AVERAGE ( 'table'[Col2] ), ALL ( 'Table'[Col1], 'Table'[Col2] ) )

Sum = 
CALCULATE ( SUM ( 'table'[Col2] ), ALL ( 'Table'[Col1], 'Table'[Col2] ) )

Best Regards,

Dale