Forum Discussion
Relationships between two columns
Hi!
A newbie to PowerBI, so sorry if the question is really basic, I just don't see the way out of what looks like a very simple problem.
I have some datasets with thousands of data, so I've simplified it to a very basic problem with two dimensions.
I have a dimension of sales and a dimension of salesman. Like this:
Sales table. Each row represents a sale, which may have up to two salesman involved. It also states the lenght of the sale.
Sales tableAnd then I have a table with the salesman names.
Salesman table
I want to get a the total time spent by each salesman: in total, as salesman 1 and as salesman 2. Ending up like this:
TOTAL time
John:10
Mike:35
Marta:0
Alex:40
Elizabeth:10
...
TOTAL time as salesman 1
John:10
Mike:20
Marta:0
Alex:40
Elizabeth:10
...
TOTAL time as salesman 2
John:0
Mike:15
Marta:0
Alex:0
Elizabeth:0
...
Does that make sense???
Thanks in advance!
Hi Anonymous
In addition to Greg_Deckler's method,
you could also transform the Sales table with Power Query as below:
go to "Edit Queries", select on "SalesmanId 1" and "SalesmanId 2", select "Unpivot columns",
Then close &&apply,
next, create relationship between two tables based on "Sales table"[value] and "salesman names"[SalesmanId]
Please find more details in my pbix.
Best regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Easiest way would be to not relate the tables and then create a measure that does two LOOKUPVALUE's and adds them together.
Hi Anonymous
In addition to Greg_Deckler's method,
you could also transform the Sales table with Power Query as below:
go to "Edit Queries", select on "SalesmanId 1" and "SalesmanId 2", select "Unpivot columns",
Then close &&apply,
next, create relationship between two tables based on "Sales table"[value] and "salesman names"[SalesmanId]
Please find more details in my pbix.
Best regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
