Fake a hierarchy so you can drill down to another level using AGGREGATED data as the source

[digimonkey]

I've been wracking my brain trying to figure out if this is possible in Power BI, but maybe someone more seasoned might be able to advise. I have a client that wants to use aggregate data (provided in summary form) and have a dashboard built off of this. I was wondering if it might be possible to fake a hierarchy (so to speak) so that the user can drill down to another level of aggregate data. Is this possible, or is this science fiction?

Just in case that wasn't clear, here's an example. The client would provide the average sales by region, but then would also provide the average sales overall. The way the data is structured right now it would be possible to view all values (region/region total) as though they are the same level, but I couldn't set the region region total as the only value displayed unless the user drilled down to the next level... Or could I?! Any help would be appreciated!

[GeraSanz11]

i don't get very well what are you trying to do but as far as i can recall

a) if you have a table like this:

Region          |   Amount

SubRegionA  |  1

SubRegionB  | 2

TotalRegion  | 3

You reconfigure it to something like this

SubName      |  Reg Name     | Amount

SubRegionA  |  TotalRegion  |    1

SubRegionB  |  TotalRegion  |    2 

Since pBI will calculate the sum / average and so on for you, you don't need to add totals

the other way i can think of is to create a new table like some sort of ID 

SubName      |  Reg Name   

SubRegionA  |  TotalRegion

SubRegionB  |  TotalRegion

and link them by "sub Region Name" with the main Table, and create the hierarchy from there

[digimonkey]

Thanks for your ideas! Unfortunately the first suggestion won't work because then I'd be taking an average of an average (which is statistically irrelevant/incorrect). With your second suggestion, I might be able to do something like this... 

SubName      |  SubRegionAverage   | Total

SubRegionA  |  RegionAAverage       | TotalRegionAverage

SubRegionB  |  RegionBAverage       | TotalRegionAverage

I'd have to use a MAX on each to get the actual average values, but this could work. The concern I'd have with this approach is then that these are separate "measures" for Power BI. Is there a way to selectively aggregate different data elements in a measure based on a user's selection? Sorry I'm really new to Power BI.

[v-caliao-msft]

@digimonkey,

When a visual has a hierarchy, you can drill down to reveal additional details. Hierarchy are created on dimension. And the value will aggregated automatically on each level. Such as : Region>Country>City... So we just need detail data. Please refer to the link below to see the details about dirll down visual in Power BI.
https://powerbi.microsoft.com/en-us/documentation/powerbi-service-drill-down-in-a-visualization/

Regards,

Charlie Liao