Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Sum up data in calculated table

Hi

I have a table with survey data. I am using Power BI Visualizations to show this data and segment by groups.

When I do page level filtering of eg one company's data, I am no longer able to show the totals and averages across all companies. I know this is by design, but I need to override it.

 

My idea for a solution was to calculate a new table with company names and their averages based on the initial table - and then reference those values in my reporting - as I believe they will not be affected by the report filter.

 

Table one has:

Company name --- Survey Result
Acme Corp        --- 4
Acme Corp        --- 7
Acme Corp        --- 10
No Such Co       --- 3
No Such Co       --- 4
No Such Co       --- 8

My calculated table should then show:

 

Company name --- Average Result
Acme Corp    --- 7
No Such Co --- 5

I hope this makes sense and someone knows the answer.

 

Br, Simon

1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

Hi Simon,

 

Give this a crack...

 

New Table = SUMMARIZECOLUMNS(
                            --- Group By ----
                            'Table1'[Company Name],
                            --- Aggregations ----
                            "Average Result" , AVERAGE(Table1[Survey Result])
                            )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
v-ljerr-msft
Employee
Employee

Hi @SimonKibsgaard,


My idea for a solution was to calculate a new table with company names and their averages based on the initial table - and then reference those values in my reporting - as I believe they will not be affected by the report filter. 

Yes, calculate table\column is evaluated during the database processing(like data refresh), it won't be affected by the report filters. And the formula provided by SimonKibsgaard should work in your scenario.Smiley Happy

 

Regards

Phil_Seamark
Employee
Employee

Hi Simon,

 

Give this a crack...

 

New Table = SUMMARIZECOLUMNS(
                            --- Group By ----
                            'Table1'[Company Name],
                            --- Aggregations ----
                            "Average Result" , AVERAGE(Table1[Survey Result])
                            )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Works! Thnx a lot.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.