Graph with dynamic (budget) lines based on different region hierarchy

[StephanievC]

 We are currently trying to make a graph which shows both the actual sales and the corresponding dynamic budget.

We have three different budgets:

-    Budget for a Region

-    Budget for a Subregion

-    Budget for a City

We have a reference table in which the different regions, subregions and cities are named with corresponding budget. Sample data tables are below. 

In all cases, we want to show the actual sales for the selected region / subregion or city. On top of that, we want to show a maximum of one budget (see image below). We have several rules which define when we would like to see a specific budget. For example, if we selected a subregion or city without an specific budget for that subregion or city, we do not want to show the budget for the region. Do you have any idea how we are able to show the aimed budget?

We want to make the following interactions between region/subregion/city with the budget:

If a Region is selected:

-    Total sales and budget for the whole region (Region, subregion and city together)

If a subregion is selected:

-    In case of subregion budget show the subregion total sales and budget

-    In case of city budget show the total subregion total sales and budget (sum of underlying cities)

-    In case of only Region budget, don’t show the budget, only show the total sales for the sub-region

If a City is selected:

-    In case of City budget show the City sales and budget

-    In case of only Region or subregion budget, don’t show the budget, only show the total sales for the City. 

Thanks in advance.

Link to pbix file with sample data: 

https://www.dropbox.com/s/71u6yzr760xrlqy/Dynamic%20budget%20region%20hierachy.pbix?dl=0

Example sales table: 

Region	Subregion	City	Date	Sales
Noord-Holland	Amstelland	Aalsmeer	5-1-2021	1150
Noord-Holland	Amstelland	Amsterdam	6-1-2021	1500
Noord-Holland	't-Gooi	Laren	7-1-2021	1750
Zuid-Holland	Drechtsteden	Papendrecht	7-1-2021	900
Zuid-Holland	Delfland	Delft	6-1-2021	1150
Zeeland	Walcheren	Arnemuiden	17-1-2021	400
Zeeland	Zuid-Beverland	Kapelle	14-1-2021	650
Groningen	Westerkwartier	Leek	20-1-2021	1000
Groningen	Westerkwartier	Marum	9-1-2021	1250
Noord-Holland	Amstelland	Aalsmeer	3-2-2021	1250
Noord-Holland	't-Gooi	Laren	10-2-2021	1600
Zuid-Holland	Delfland	Delft	24-2-2021	1050
Zuid-Holland	Albasserwaard	Albasserdam	15-2-2021	900
Zeeland	Walcheren	Arnemuiden	6-2-2021	650
Zeeland	Zuid-Beverland	Kapelle	4-2-2021	900
Groningen	Westerkwartier	Leek	6-2-2021	1250
Noord-Holland	Amstelland	Aalsmeer	8-3-2021	1750
Noord-Holland	Amstelland	Amsterdam	14-3-2021	1750
Noord-Holland	't-Gooi	Laren	18-3-2021	1250
Zuid-Holland	Drechtsteden	Papendrecht	24-3-2021	1250
Zuid-Holland	Delfland	Delft	16-3-2021	975
Zuid-Holland	Albasserwaard	Albasserdam	20-3-2021	1500
Zeeland	Walcheren	Arnemuiden	7-3-2021	1000
Zeeland	Zuid-Beverland	Kapelle	12-3-2021	775
Groningen	Westerkwartier	Leek	30-3-2021	1250
Groningen	Westerkwartier	Marum	17-3-2021	975

Example region table: 

Region	Subregion	City
Noord-Holland	Amstelland	Aalsmeer
Noord-Holland	Amstelland	Amsterdam
Noord-Holland	't-Gooi	Laren
Zuid-Holland	Albasserwaard	Meerkerk
Zuid-Holland	Delfland	Delft
Zuid-Holland	Albasserwaard	Albasserdam
Zeeland	Walcheren	Arnemuiden
Zeeland	Zuid-Beverland	Kapelle
Groningen	Westerkwartier	Leek
Groningen	Westerkwartier	Marum

Example budget table: 

Region	Subregion	City	Budget
Groningen			1150
Noord-Holland	Amstelland		1450
Noord-Holland	't-Gooi		1375
Zeeland	Walcheren	Arnemuiden	750
Zeeland	Zuid-Beverland	Kapelle	800
Zuid-Holland	Alblasserwaard	Albasserdam	1175
Zuid-Holland	Delfland		1025
Zuid-Holland	Alblasserwaard	Meerkerk	1250

[Anonymous]

Hi @StephanievC ,

Please try this:

Sales =SWITCH(TRUE(),
ISFILTERED(Regions[Region]), 
  CALCULATE(SUM(Sales[Sales]),FILTER('Sales','Sales'[Region] in ALLSELECTED(Sales[Region]))), 
ISFILTERED('Regions'[Subregion]),
  CALCULATE(SUM(Sales[Sales]),FILTER('Sales','Sales'[Subregion] in ALLSELECTED(Sales[Subregion])))

Actually I'm a little confused about:
the In case of only Region budget, don’t show the budget, only show the total sales for the sub-region

What does only Region mean... ?Please provide me with more details about your problem.

Best Regards,
Eyelyn Qin