Aggregate an existing table into one "global" metric for drill down

[sosih]

Hi!

I want to see my results in a table on a global level as well as on a country level.

This is the dummy data that I have today, and I have created a table in PBI with this:

Market	Total sends	Open rate	Revenue
AU	19234	12.30%	€  2,368,236
CZ	89374	12.50%	€  2,368,233
CH	43975	11.90%	€  2,368,854
NL	9375	11.83%	€     230,384
SE	93752	11.63%	€     236,823
DE	94734	11.43%	€  2,364,844
DK	4532	11.23%	€     233,242

What I want is to aggregate all columns into one row (mix of SUM and AVERAGE) that I can drill down into:

Market	Total sends	Open rate	Revenue
Global view	354976	11.83%	€   10,170,616
AU	19234	12.30%	€  2,368,236
CZ	89374	12.50%	€  2,368,233
CH	43975	11.90%	€  2,368,854
NL	9375	11.83%	€     230,384
SE	93752	11.63%	€     236,823
DE	94734	11.43%	€  2,364,844
DK	4532	11.23%	€     233,242

**EDIT: Underneath market there will be different level of campaign info - the global view must follow the same hierarchy. Otherwise I could have just used the total row as a solution.

Appreciate all help and ideas!

[sosih]

Thank you both! I had to convince my organisation to upgrade to the latest PowerBi version. It worked and I really like the new outline function.

Big thanks!

[Anonymous]

Hi @amitchandak ,Thanks for your quick reply, I will add more.

Hi @sosih ,

Does @amitchandak  answer meet your needs? I think it does.The results of the total row will change according to your hierarchy changes.You just need to make sure that the measure 'Total sends' is calculated as sum and the measure 'Open Rate' is calculated as average. The total row is calculated based on this context.

If I understand wrongly, please correct me.

Best Regards,
Wenbin Zhou

[amitchandak · Accepted solution]

@sosih, In the new Matrix Visual layout should be able to help you - Outline , if needed use repeat header. And rename GT

New Matrix Layouts | Repeating Row Headers| Power BI Concepts: https://www.youtube.com/watch?v=f_DOvb_PDG0&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L