Measure to display related country budget

[xmperali]

Below is Budget Allocations table to see ,how much Countries budget is allocated to state and there by cities and how much budget is remained.(giving background) because sum of all states budget is not equal to countries budget
and sum of all cities budget is not equal to state budget.

My question , i want to create a measure in PowerBI, so that when i filter using country name it should show related country budget. Could you please help me in creating measure.

Business key is CityName

CountryName	Country Budget	StateName	State Budget	CityName	CityBudget
A	10	A1	8	A11	4
A	10	A1	8	A21	4
B	20	B1	18	B11	18
C	20	C1	20	C11	20
D	30	D1	30	D11	10
D	30	D1	30	D21	10
D	30	D1	30	D31	10

[technolog · Accepted solution]

Given the structure of your table, you can create a measure using the DAX function SUMX combined with the DISTINCT function to ensure you're not summing up the same country's budget multiple times.

Here's a DAX measure that should do the trick:

Country Budget Measure =
SUMX(
DISTINCT('Budget Allocations'[CountryName]),
CALCULATE(SUM('Budget Allocations'[Country Budget]))
)
This measure works by first creating a distinct list of country names (so each country is only considered once) and then, for each country, it sums up the country budget. When you apply a filter on the country name, this measure will show the budget for that specific country.