Forum Discussion
DAX Measure for Sum over m-n relationship
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,
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.
Regards,
Daniel He
1 Reply
- v-danhe-msft
Microsoft Employee
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.
Regards,
Daniel He