Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am stuck in one problem in Power Bi, any help would be appreciated.
I have two tables in power BI 1) Account 2) Sales
Account table has two columns 1) id 2) Owner
Sales has four column 1)Sales(YTD) 2)Accid 3) trader 4)Owner
I have built relationship between them by using unique id column from account table and id column from Sales table
Now the situation is that account table owner column represents the owner of the account,
But for that perticular account other owner can also do sales.
Now when I am trying apply filter to filter sales data based on owner it is only giving value for the account of which he is a owner
But I want all the account for which he has made the sales.
Please find sample data
Sales table
Trader | YTD | AccID | Owner |
PASCAL SELTZER | 0 | 4480A27A-46B8-E811-A978-000D3AF49C6C | Pascal Seltzer |
PASCAL SELTZER | 0 | 3AC1D8F1-F89B-EC11-B3FE-000D3A09D328 | Pascal Seltzer |
DANIEL MADIO | 3,251.10 | 7C80A27A-46B8-E811-A978-000D3AF49C6C | Shaker Droubi |
DANIEL MADIO | 2,595.72 | 37EA4450-D698-E911-A982-000D3AF490CC | Daniel Madio |
MICHAEL POIRIER | 4,296.67 | B3A02A02-2C78-EA11-A811-000D3AF46489 | Michael Poirier |
MICHAEL POIRIER | 2,595.70 | 37EA4450-D698-E911-A982-000D3AF490CC | Daniel Madio |
RACHID BELKHADIR | 4,296.67 | B3A02A02-2C78-EA11-A811-000D3AF46489 | Michael Poirier |
SHAKER DROUBI | 9,160.80 | 2836F63D-A875-EA11-A811-000D3AF46489 | Christopher Pratt |
Account table
AccID | Owner |
4480A27A-46B8-E811-A978-000D3AF49C6C | Pascal Seltzer |
3AC1D8F1-F89B-EC11-B3FE-000D3A09D328 | Pascal Seltzer |
7C80A27A-46B8-E811-A978-000D3AF49C6C | Shaker Droubi |
37EA4450-D698-E911-A982-000D3AF490CC | Daniel Madio |
B3A02A02-2C78-EA11-A811-000D3AF46489 | Michael Poirier |
2836F63D-A875-EA11-A811-000D3AF46489 | Christopher Pratt |
Thanks for any help in advance.
Solved! Go to Solution.
Try
Total sales by trader =
CALCULATE (
SUM ( Sales[YTD] ),
REMOVEFILTERS ( Account ),
TREATAS ( VALUES ( Account[Owner] ), Sales[Trader] )
)
Thanks for your time and reply,
It is not working as exepted.
The thing is that I have one table visual with columns Accid from Account table and Sales, One filter with Owner coming from account table,
Now when I am trying to filter data by selecting any owner from filter it is taking Owner from account matching it with owner column in sales table, Instead of that it should match it with Trader column in sales table.
As per above sample, Suppose I am selecting
Daniel Madio |
Than output in table should be------Right
7C80A27A-46B8-E811-A978-000D3AF49C6C |
3,251.10 |
37EA4450-D698-E911-A982-000D3AF490CC
2,595.72
It is giving me output as----- Wrong
37EA4450-D698-E911-A982-000D3AF490CC
2,595.72
37EA4450-D698-E911-A982-000D3AF490CC
2,595.70 |
I've edited my original post to use the correct column.
I've tried by that way but it is giving same value for all the account in table.
It works for me using the sample data if I put the account id from the sales table in the visual
Yes, that way it will work but I do have some other data in table, So I have to use accounts from Account table only
It works for me that way too
Thanks for everything, But if you can share me PBIX file it will be very helpful!
Thanks for The file, Will have a look and let you know
Thanks again
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
46 | |
31 | |
30 | |
18 | |
18 |