DAX Measure for Sum over m-n relationship

[Raffael]

Hello Power BI Community,

i have troubles with a dax measure to calculate a sum over several tables.

i have one table to allocate certain people to defined regions via ids.  those ids are linked to a list of adimistrative regions via the ids (1-*) (one adm. region has one or multiple ids allocated to it). the administrative regions are linked to a list of distinct administrative regions (1-*). to the same list a table with region names and sales is linked (*-1)

tl:dr: sales person 1-* (via ids) admin region *-1 distinct admin region 1-* sales figues

what i want is to create a measure that sums up the sales figure for each sales person. I am able sum up the sales figures for the distinct admin regions, i can also slice all distinct admin regions by the sales person's name.

However i can not create the sum for all selected admin regions when slicing by a sales person. Seems to be some context issue i can'T wrap my head around as i always get the total sum, no matter the slicing.

Can somebody help me creating a measure to sum up the sales figures for the individual sales persons?

Best Regards,

[v-danhe-msft · Accepted solution]

Hi @Raffael,

Based on my test, you could refer to below steps:

Sample data:

Creaete a measure

Measure = CALCULATE(AVERAGE('sales figues'[Sales]),FILTER('sales person','sales person'[id]=MAX('admin region'[Id])))

Result:

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/c0v7rfewie0vhfw/DAX%20Measure%20for%20Sum%20over%20m-n%20relationship.pbix...

Regards,

Daniel He