Division in Matrix for Hierarchy

[Anonymous]

Hello!

I have been trying to figure out how to get the division to work for matrix tables in Power BI. It works for the lower hierarchy level but not the higher level. Any suggestions would be much appreciated! See screenshot below. The higher level shows 100% while the lower level % is correct.

I realized that I can't upload a sample workbook so I'm pasting the tables and the calculations here.

Tables

Sheet1

Country	Location	Budget	Location Expenses
USA	Seattle	650	600
USA	NY	800	790
USA	Chicago	750	600
USA	Dallas	550	300
USA	Miami	600	450
UK	London	650	590
UK	Liverpool	600	300
UK	York	550	250
Canada	Vancouver	650	598
Canada	Toronto	680	652
China	Beijing	540	325
China	Shanghai	600	589

Budget

Country	Location	Award Value
USA	Seattle	50
USA	NY	60
USA	Chicago	35
USA	Dallas	40
USA	Miami	36
UK	London	55
UK	Liverpool	25
UK	York	15
Canada	Vancouver	80
Canada	Toronto	77
China	Beijing	45
China	Shanghai	62

point adj

Country	Location	Type	Points
USA	Seattle	Location Transfer	100
USA	Seattle	Location Holdback	35
USA	Seattle	Point Addition	25
USA	NY	Location Transfer	200
USA	NY	Location Holdback	50
USA	NY	Point Addition	50
USA	NY	Point Addition	10
USA	Chicago	Point Addition	25
USA	Chicago	Location Holdback	45
USA	Dallas	Location Holdback	100
USA	Miami	Location Transfer	30
USA	Miami	Location Holdback	50
UK	London	Location Transfer	25
UK	London	Location Holdback	30
UK	London	Point Addition	100
UK	Liverpool	Location Holdback	45
UK	York	Location Holdback	75
Canada	Vancouver	Point Addition	100
Canada	Vancouver	Location Holdback	40
Canada	Toronto	Point Addition	95
Canada	Toronto	Location Holdback	50
China	Beijing	Location Transfer	200
China	Beijing	Location Holdback	25
China	Shanghai	Location Holdback	100

Table relationships:

budget to Sheet1 through Location: one to one

point adj to Sheet1 through Location: Many to one

Calculations:

Location Holdback = CALCULATE(SUM('point adj'[Points]), FILTER('point adj','point adj'[Location] = SELECTEDVALUE(Sheet1[Location]) && 'point adj'[Type] = "Location Holdback"))

Point Adjustments = (CALCULATE(SUM('point adj'[Points]), FILTER('point adj', 'point adj'[Location] = SELECTEDVALUE(Sheet1[Location])), FILTER('point adj','point adj'[Type] = "Point Addition"))) + (CALCULATE(SUM('point adj'[Points]), FILTER('point adj', 'point adj'[Location] = SELECTEDVALUE(Sheet1[Location])), FILTER('point adj','point adj'[Type] = "Location Transfer")))

Budget after = SUM(Sheet1[Budget]) - [Location Holdback] + [Point Adjustments]

Points Spent = CALCULATE(-SUM(budget[Award Value]), FILTER(budget,budget[Location] = SELECTEDVALUE(Sheet1[Location])))

Points Remaining = [Budget after] + [Points Spent]

% = DIVIDE([Points Remaining], [Budget after], 0)

[amitchandak]

@Anonymous, create measures like

Location Holdback = CALCULATE(SUM('point adj'[Points]), FILTER('point adj','point adj'[Type] = "Location Holdback"))

Point Adjustments = (CALCULATE(SUM('point adj'[Points]), FILTER('point adj', FILTER('point adj','point adj'[Type] = "Point Addition"))) + (CALCULATE(SUM('point adj'[Points]), FILTER('point adj','point adj'[Type] = "Location Transfer"))) )

Budget after = SUM(Sheet1[Budget]) - [Location Holdback] + [Point Adjustments]

Points Spent = -1* SUM(budget[Award Value])

Points Remaining = [Budget after] + [Points Spent]

% = DIVIDE([Points Remaining], [Budget after], 0)

[Anonymous]

Hi @amitchandak , thank you SO MUCH for responding to my post. Actually I just figured out that I should have used the column in the % calculation instead of the measure.

Thank you though!