Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm in need of a solution, which I'm sure is possible, but beyond my current PowerBI skill-set.
Thanks in advance for your help.
I have a table with 4 columns:
The purpose of the table is to trace the moment of inventory transactions through the system - from the initial inbound/increase entry through to whe the inventory left/decreased.
Below are some examples of increasing complexity:
Example 1
Required result I need is
Example 2 - slightly more complex
Required result I need is
Example 3 - slightly more complex again
Required result I need is
Example 4 - another slightly more complex scenario
Required result I need is
Hopefully from these examples below you can see the table I'm trying to build could be described as: "for each inbound entry (defined as where [Entry No. (IN)] not blank AND [Entry No. (OUT)] = 0) I need to know where and in what quantity it has ended up."
Where it ended up will always be the MAX([Entry No. (OUT)]). However it's not a rule that the [Entry No. (IN)] is always less than the [Entry No. (OUT)], sometimes (IN) will be higher than (OUT), however the MAX([Entry No. (OUT)] will always be the "where it went" for the higher (IN). example:
Required result I need is
You'll see fomr the examples also that any quantities yet to be fully used/consumed should also be visible.
below is a larger set of example data and expected results.
Row ID | Entry No. | Entry No. (IN) | Entry No. (OUT) | Quantity |
1 | 1 | 1 | 0 | 50 |
2 | 2 | 1 | 2 | 50 |
3 | 3 | 3 | 0 | 50 |
4 | 4 | 3 | 4 | 25 |
5 | 5 | 3 | 5 | 25 |
6 | 6 | 4 | 6 | 13 |
7 | 7 | 4 | 7 | 12 |
8 | 8 | 8 | 0 | 50 |
9 | 9 | 8 | 9 | 30 |
10 | 10 | 9 | 10 | 15 |
11 | 11 | 9 | 11 | 15 |
12 | 12 | 10 | 12 | 7 |
13 | 13 | 10 | 13 | 8 |
14 | 14 | 11 | 14 | 10 |
15 | 15 | 15 | 0 | 50 |
16 | 16 | 15 | 16 | 10 |
17 | 17 | 15 | 17 | 10 |
18 | 18 | 15 | 18 | 10 |
19 | 19 | 16 | 19 | 5 |
20 | 20 | 17 | 20 | 7 |
I'm not sure (a full analysis would take quite a bit of time) but if I were to tackle this... I would look for a better model for my data. Certainly one that'd be clearer and easier from the point of view of data modeling (think: star schema).
Hi Daxer,
Thanks for your response, unfortunately I'm working with a dataset that is fixed by Microsoft so not something that is able to be changed; otherwise i would be doing exactly that and building a better data model from the start. It is actuyally a very effective datamodel, and is effectively a for.. each.. next.. loop which I would be able to do it in SQL even with my basic knowledge there; i'm just not sure how to replicate the same in PowerBI.
At the moment i'm looking to create a series of left join merge queries in PowerBI and then a final IF statemtent to grab wherever the last value in each row was found... but i'm certain there is a slicker way hence the community post.
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |