Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculating value from different rows in the same data

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 ProductQuantity
AAP1100
AAP2200
AA-1P120
AA-2P150
AA-1P240
AA-2P2100
BBP1100
BBP2200
BB-1P120
BB-2P150
BB-1P240
BB-2P2100

 

 

Data from the second system 

Companie `Sub companie ProductQuantity True Quantiy
AA-1P120 20
AA-2P150 50
AA-1P240 40
AA-2P2100 100
BB-1P120 20
BB-2P150 50
BB-1P240 40
BB-2P2100 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 ProductQuantity True Quantiy
AAP1100 30
AAP2200 60
AA-1P120 20
AA-2P150 50
AA-1P240 40
AA-2P2100 100
BBP1100 30
BBP2200 60
BB-1P120 20
BB-2P150 50
BB-1P240 40
BB-2P2100 100
AA-1P120 20
AA-2P150 50
AA-1P240 40
AA-2P2100 100
BB-1P120 20
BB-2P150 50
BB-1P240 40
BB-2P2100 100

 

I would appreciate if someone here can help me to solve this issue. 

 

Thank you

Koffi

1 REPLY 1
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.