Table Display

[Anonymous]

Hello,

We are creating a dashboard in Power BI and are having difficulty with a display request from our users.  I am hoping you can help with that because I don't know how to start this.

Our company receives shipments into warehouse 0002 and then transfer items to our other warehouses that need replenishment.  This dashboard is going to be used by a person in warehouse 0002 and help her determine which warehouse she needs to create a transfer to (specific to that ITEM).  

Our table consists of three columns:

ITEM – this is the item that may or may not need a transfer

WHSE – displays the warehouse number

WHSE QTY – shows the qty of that item.  If it is positive, we have inventory.  If it is negative, that warehouse is demanding inventory.

Here are some conditions that we are looking to satisfy for the development of this dashboard.

• In all cases, we never want to show a line for the ITEM at WHSE 0002.  We only want to display which WHSE that ITEM needs to be transferred to.

• If WHSE (0002) has a WHSE QTY that is equal to the demand to another warehouse for that ITEM then show ONLY the demanding warehouse.  If there is more than one demanding warehouse for that ITEM then we only need to display one WHSE (it doesn’t matter which demanding warehouse is displayed).  In the example below, WHSE 0002 can fill the demand for WHSE 0004 and 0030.  We only need to display one warehouse so we would like the output to only show the row in the middle.  We don’t need to see the first and third row.

ITEM	WHSE	WHSE QTY
258963	0002	3
258963	0004	-3
258963	0030	-3

• If WHSE (0002) has a WHSE QTY that is equal to zero or is negative for that ITEM, do not display an output for that ITEM (for any WHSE).  In the example below, WHSE 0002 does not have inventory to transfer so no need to display either row.

ITEM	WHSE	WHSE QTY
7013	0002	-2
7013	0032	1

• If WHSE (0002) has a WHSE QTY that is positive for that ITEM AND the WHSE QTY at ALL of the other WHSE locations are positive for the same ITEM then do not display any of the results.   In the example below, all of the warehouses have a positive WHSE QTY and none of these rows need to be displayed.

ITEM	WHSE	WHSE QTY
7016	0002	1
7016	0032	1
7016	0004	1

• If WHSE (0002) does not have an ITEM listed, then do not show any rows for that ITEM.  In the below example, WHSE 0002 does not carry that item so it cannot help replenish the demand for that ITEM at the other WHSE's.

ITEM	WHSE	WHSE QTY
8478	0032	-1
8478	0004	-9

Here is some sample data, if helpful.  The examples above, appear here.  This community has been really great.  I've learned so much from here.  I hope to be as proficient at Power BI as all of you are soon so that I can give back and start helping others!

ITEM	AVAILABLE WHSE	WHSE QTY
101	0002	-2
101	0030	-2
101	0032	-2
101	0004	-2
1232.15882	0002	9
1232.15882	0032	2
3013	0030	2
3103	0030	-3
3103	0002	-3
3103	0032	-3
3103	0004	-3
7013	0002	-2
7013	0032	1
7016	0002	1
7016	0032	1
7016	0004	1
7204	0030	1
7204	0002	2
8478	0032	-1
8478	0004	-9
9550	0030	-9
9550	0002	58
9550	0032	-9
17817	0002	247
17817	0032	-53
21884	0002	258
21884	0032	-36
056-0207	0030	5
056-0207	0002	2
055FBSH015+054S01s806	0002	1
055FBSH015+054S01s806	0034	-1
258963	0002	3
258963	0004	-3
258963	0030	-3
076-0485A	0002	2
076-0585A	0002	1
076-5271	0002	2
076-5721	0002	8
8 019 51 002	0037	2
8 030 44 101	0001	3
716L1800	0002	6
8 030 56 096	0035	2
716L1900	0002	2
8 039 55 064	0037	2
80.110.11NG	0001	1
71915CEHC-P4A-DGA	0002	1
71916CE/P4A/QBTA	0002	15
7200CDGNP5	0002	8
7202CDBGNP5	0002	3
80.124.00	0001	1
7203DB	0002	2
7204CTRDULP4Y	0002	1
7206ATYDBTP5	0002	2
7206BYG	0002	5
80063001	0001	1
800A-K2AW	0037	2
800A-M2AC4L	0037	1
800A-M2EG	0037	5
800E-2X10	0037	7
800E-A2L	0037	5
800EM-F2	0037	2
800EM-HR22	0037	1

[speedramps]

Sorry this is not a finished solution, but I am a volunteer who provides free BI support in my spare time and it is now past my bed time.

Click here to download a solution 

I have used Power Query to create 3 tables:-

• Items (with item code)
• HQ (with inventory and  demand just for WHSE 0002)
• Local (with inventory and  demand just for WHSE 0002)

Then  joined the tables with relationships

Then created DAX measure for 

• HQ invetory
• HQ demand
• Local invetory
• Local demand

This makes building reports much easier

The left visual show just items that have HQ invetory  AND  Local demand.

If you click on an item on left visual then the right hand visual will show the local warehouses that have the demand.

It needs tweaking for your exact requirements but I hope that this first stab helps!

I will try have a look again tomorrow in my lunch break.  😁