Create a Measure with an indirect relation

[debiagui]

Hi I need help to create a measure to summarize the sales for an employee, but in my scenario, I've some cases where the employee has customers assigned but in some cases the employee has a sales area or a channel or a any geografic customer data.

My model has four tables.

One for customers with gographic info

One for sales by period and by customer

One fo employee

And one with the relation for an employee and customers or geografic.

My idea is create a table or matrix that shows the total sales for the associate related his relation with customers o terrotories.

[v-henryk-mstf · Accepted solution]

Hi @debiagui ,

Your statement seems a bit vague, whether the four tables provided are correct and whether there is a relationship between them. Can you give an example, such as how 57,418 corresponding to 1 of EmployeeId in the result is obtained. I will answer you as soon as possible.

Looking forward to your reply.

Best Regards,
Henry

[Greg_Deckler]

@debiagui Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[debiagui]

Sorry about this, @Greg_Deckler, I create a simple example in Excel here:

https://file.io/Z17ZEEiPDBMF

Hope this helps.-

[Greg_Deckler]

Says deleted.

[debiagui]

Here we are 🙂

Employee

EmployeeId	EmployeeName
1	A
2	B
3	C
4	D

Customers:

CustomerId	CustomerName	Country	Channel	Territory
1	A	Argentina	Supermarket	A
2	B	Chile	Supermarket	B
3	C	Argentina	E-Commerce	C
4	D	Chile	Supermarket	F
5	C	Argentina	C-Store	D
6	D	Argentina	C-Store	D

Relations:

EmployeeId	Country	Channel	Territory	CustomerId
1	Argentina
2	Chile	Supermarket
3	Argentina	C-Store	D	5
4	Chile	Supermarket	F
3	Argentina	C-Store	D	6

Sales:

Year	Month	CustomerId	SalesAmount
2021	January	1	$          5,599.00
2021	January	2	$          1,387.00
2021	January	3	$          8,944.00
2021	January	4	$          7,512.00
2021	January	5	$          8,660.00
2021	January	6	$          9,247.00
2021	February	1	$          2,220.00
2021	February	2	$          1,121.00
2021	February	3	$          3,196.00
2021	February	4	$                40.00
2021	February	5	$             463.00
2021	February	6	$          2,860.00
2021	March	1	$          7,114.00
2021	March	2	$          5,461.00
2021	March	3	7,073.00

Result

SalesAmount

EmployeeId	EmployeeName	SalesAmount
1	A	57,418
2	B	40,740
3	C	25,275
4	D	15,370