Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear all,
I am facing a data summary problem with my data which has duplicated values
The system automatically receive from two different sources the data a a different timing
and as of today we do not have possibility to preprocess the data before it get into the data base
Following is the data I have to get from the DB and do my reports in Power bi
Data from the first sytem
Companie | `Sub companie | Product | Quantity |
A | A | P1 | 100 |
A | A | P2 | 200 |
A | A-1 | P1 | 20 |
A | A-2 | P1 | 50 |
A | A-1 | P2 | 40 |
A | A-2 | P2 | 100 |
B | B | P1 | 100 |
B | B | P2 | 200 |
B | B-1 | P1 | 20 |
B | B-2 | P1 | 50 |
B | B-1 | P2 | 40 |
B | B-2 | P2 | 100 |
Data from the second system
Companie | `Sub companie | Product | Quantity | True Quantiy | |
A | A-1 | P1 | 20 | 20 | |
A | A-2 | P1 | 50 | 50 | |
A | A-1 | P2 | 40 | 40 | |
A | A-2 | P2 | 100 | 100 | |
B | B-1 | P1 | 20 | 20 | |
B | B-2 | P1 | 50 | 50 | |
B | B-1 | P2 | 40 | 40 | |
B | B-2 | P2 | 100 | 100 |
In order to report the right numbers for each sub-company
I need to create a Calculated column or measure that
allows me to substract the quantity that is included in its company.
For example for the product P1
Quantiy of Sub-company A =100-(20+50) = 30
So the final result would look like this
Companie | `Sub companie | Product | Quantity | True Quantiy | |
A | A | P1 | 100 | 30 | |
A | A | P2 | 200 | 60 | |
A | A-1 | P1 | 20 | 20 | |
A | A-2 | P1 | 50 | 50 | |
A | A-1 | P2 | 40 | 40 | |
A | A-2 | P2 | 100 | 100 | |
B | B | P1 | 100 | 30 | |
B | B | P2 | 200 | 60 | |
B | B-1 | P1 | 20 | 20 | |
B | B-2 | P1 | 50 | 50 | |
B | B-1 | P2 | 40 | 40 | |
B | B-2 | P2 | 100 | 100 | |
A | A-1 | P1 | 20 | 20 | |
A | A-2 | P1 | 50 | 50 | |
A | A-1 | P2 | 40 | 40 | |
A | A-2 | P2 | 100 | 100 | |
B | B-1 | P1 | 20 | 20 | |
B | B-2 | P1 | 50 | 50 | |
B | B-1 | P2 | 40 | 40 | |
B | B-2 | P2 | 100 | 100 |
I would appreciate if someone here can help me to solve this issue.
Thank you
Koffi
I would break the first table into 2 tables. The first query would just get all rows where the "Companie" equaled teh "Sub companie". The other query would get the reverse of that. Then you could relate the tables and the problem should become elementary using a CALCULATE coupled with a RELATEDTABLE filter.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |