topic Re: Need Help with Power bi in DAX Commands and Tips

Need Help with Power bi

Anonymous — Tue, 06 Jun 2023 19:10:51 GMT

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.

Re: Need Help with Power bi

johnt75 — Wed, 07 Jun 2023 15:24:46 GMT

Try

Total sales by trader = CALCULATE ( SUM ( Sales[YTD] ), REMOVEFILTERS ( Account ), TREATAS ( VALUES ( Account[Owner] ), Sales[Trader] ) )

Re: Need Help with Power bi

Anonymous — Wed, 07 Jun 2023 14:39:07 GMT

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

Re: Need Help with Power bi

johnt75 — Wed, 07 Jun 2023 15:25:15 GMT

I've edited my original post to use the correct column.

Re: Need Help with Power bi

Anonymous — Wed, 07 Jun 2023 16:01:52 GMT

I've tried by that way but it is giving same value for all the account in table.

Re: Need Help with Power bi

johnt75 — Thu, 08 Jun 2023 08:30:49 GMT

It works for me using the sample data if I put the account id from the sales table in the visual

Re: Need Help with Power bi

Anonymous — Thu, 08 Jun 2023 09:00:48 GMT

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

Re: Need Help with Power bi

johnt75 — Thu, 08 Jun 2023 09:48:06 GMT

It works for me that way too

Re: Need Help with Power bi

Anonymous — Thu, 08 Jun 2023 17:47:01 GMT

Thanks for everything, But if you can share me PBIX file it will be very helpful!

Re: Need Help with Power bi

johnt75 — Fri, 09 Jun 2023 08:19:25 GMT

Here you go

Re: Need Help with Power bi

Anonymous — Fri, 09 Jun 2023 11:15:32 GMT

Thanks for The file, Will have a look and let you know

Thanks again