Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Hi Simon,
Give this a crack...
New Table = SUMMARIZECOLUMNS( --- Group By ---- 'Table1'[Company Name], --- Aggregations ---- "Average Result" , AVERAGE(Table1[Survey Result]) )
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.
Regards
Hi Simon,
Give this a crack...
New Table = SUMMARIZECOLUMNS( --- Group By ---- 'Table1'[Company Name], --- Aggregations ---- "Average Result" , AVERAGE(Table1[Survey Result]) )
Works! Thnx a lot.