I'm stuck now at some point. Here's the situation:
The data:
| Offset Account Name | AccountNo | Amount | Purpose | Invoice Number |
|---|---|---|---|---|
| Customer1 | 100 | 150$ | "Customer1/Blahblah" | Inv001 |
| Customer1 | 100 | 250$ | "Customer1/Meow" | Inv002 |
| Distributor1 | 200 | 100$ | "Customer1/Inv001" | Inv003 |
| Distributor1 | 200 | 150$ | "Customer1/Inv002" | Inv004 |
| Distributor1 | 200 | 50$ | "Customer1/Inv002" | Inv005 |
My Goal? According to the example I need to extract the "InvNoXXX" from the "Purpose" column, match the entries from "Invoice Number" and then calculate the sum.
The results of the "calculate sum" should be then:
| Offset Account Name | AccountNo | Amount | Purpose | Invoice Number | Distributor |
|---|---|---|---|---|---|
| Customer1 | 100 | 50$ | "Customer1/Blahblah" | Inv001 | Distributor 1 |
| Customer1 | 100 | 50$ | "Customer1/Blahblah" | Inv002 | Distributor 1 |
As you can see, the each amounts from the distributors have been summarized and then substracted from the according amount of the matched "Invoice Number" from each "Customer-Row". And as a bonus, I need to show the according distributor in an new column.
The Point is: The data is currently only imported from an excel file and I'm trying to calculate everything within Power BI. I know how to do it in SQL, but in Power BI I'm lost.
Any ideas? Is this even possible in Power BI?
Thanks in advance!
If I understood you right, then you want to link the customer values to the distributor values?
I suggest you two create two querys from the data source with two filters on it (Transform Button when you import data). Then you have two Tables looking like this:
Customer Table:
| Offset Account Name | AccountNo | Amount | Purpose | Invoice Number |
|---|---|---|---|---|
| Customer1 | 100 | 150$ | "Customer1/Blahblah" | Inv001 |
| Customer1 | 100 | 250$ | "Customer1/Meow" | Inv002 |
Distributor Table:
| Offset Account Name | AccountNo | Amount | Purpose | Invoice Number |
|---|---|---|---|---|
| Distributor1 | 200 | 100$ | "Customer1/Inv001" | Inv003 |
| Distributor1 | 200 | 150$ | "Customer1/Inv002" | Inv004 |
| Distributor1 | 200 | 50$ | "Customer1/Inv002" | Inv005 |
After that, create a new column in the Distributor Table, add the value from Purpose column and replace everything before '/'.
At the end you have to link the Invoice Number from the Customer table Column to your new created "Invoice Column" in the Distributor table in the model view.