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
Czempijan
Helper II
Helper II

Iterate value with unrelated values by each row

Hi,

 

I have 2 unrelated tables. Let's call the yellow one below with amount to be split and the second one customer table. I woudl like to add data from yellow one to blue one and split amount from yellow one (given value for each BA) based on revenue ratio but only for each BA from customers table as shown below:

 

 

Capture.PNGDoes anyone know how to do it??? Thank you in advance.

8 REPLIES 8
Greg_Deckler
Super User
Super User

Sample data as text please. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ 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...

and here comes second table:

DateDate EDWCustomerRevCOSBAOrderCustomer pther name
04-05-20202007abradab30022PQER1aaa
04-05-20202007abradab2233PQER1aa
04-05-20202007abradab3321PQER2aa
04-05-20202007abradab133PQER3sss
04-05-20202007abradab11113444PQER3ss
04-05-20202007abradab22227PQER4d
04-05-20202007bladarab34447PQER5ddd
04-05-20202007bladarab454657PQER5dd
04-05-20202007bladarab6868744PQER7dd
04-05-20202007bladarab79755PQER765d
04-05-20202007bladarab245565757PQER8798zet

@Czempijan  In the first table you still have duplicate rows (key values). Unless there is a uniquely identifying characteristic, it is hard to see how you will pick the 700 number. Is there an order number that links the two tables? 

so the correct yellow table as follows:

DateDate EDWRTMTypeBAAmount
04-05-20202007DirectHedgePQTR100
04-05-20202007DirectHedgePQEA200
04-05-20202007DirectHedgePQEB300
04-05-20202007DirectHedgePWEC400
04-05-20202007DirectHedgePQED500
04-05-20202007DirectHedgePQEF600
04-05-20202007DirectHedgePQER700
04-05-20202007DirectHedgePIUY800
04-05-20202008DirectHedgePQTR900
04-05-20202008DirectHedgePQEA1000
04-05-20202008DirectHedgePQEB1100
04-05-20202008DirectHedgePWEC1200
04-05-20202008DirectHedgePQED1300
04-05-20202008DirectHedgePQEF1400
04-05-20202008DirectHedgePQER1500
04-05-20202008DirectHedgePIUY1600
04-05-20202009DirectHedgePQTR1700
04-05-20202009DirectHedgePQEA1800
04-05-20202009DirectHedgePQEB1900
04-05-20202009DirectHedgePWEC2000
04-05-20202009DirectHedgePQED2100
04-05-20202009DirectHedgePQEF2200
04-05-20202009DirectHedgePQER2300
04-05-20202009DirectHedgePIUY2400

No there is no link between the tables. 700 is for one BA PQER ( I think I have made mistake while pasting sample data as I put 100 againts PQER not 700):

04-05-20202007DirectHedgePQER700

 

 

Text attached I hope this is what you have expected?

 

DateDate EDWRTMTypeBAAmount
04-05-20202007DirectHedgePQER100
04-05-20202007DirectHedgePQEA200
04-05-20202007DirectHedgePQEB300
04-05-20202007DirectHedgePWEC400
04-05-20202007DirectHedgePQED500
04-05-20202007DirectHedgePQEF600
04-05-20202007DirectHedgePQTR700
04-05-20202007DirectHedgePIUY800
04-05-20202008DirectHedgePQER900
04-05-20202008DirectHedgePQEA1000
04-05-20202008DirectHedgePQEB1100
04-05-20202008DirectHedgePWEC1200
04-05-20202008DirectHedgePQED1300
04-05-20202008DirectHedgePQEF1400
04-05-20202008DirectHedgePQTR1500
04-05-20202008DirectHedgePIUY1600
04-05-20202009DirectHedgePQER1700
04-05-20202009DirectHedgePQEA1800
04-05-20202009DirectHedgePQEB1900
04-05-20202009DirectHedgePWEC2000
04-05-20202009DirectHedgePQED2100
04-05-20202009DirectHedgePQEF2200
04-05-20202009DirectHedgePQTR2300
04-05-20202009DirectHedgePIUY2400

 

 

 
sanimesa
Post Prodigy
Post Prodigy

@Czempijan  Your example is not very clear but it looks like you could benefit from adding a dimension table for unique BAs and a calendar table. Then you will build relationships from the dimension tables to the two fact tables. Finally you will be able to build aggrergate measures for totals and ratios. 

 

 

It will not work as I will not be able to filter by customer in both directions and this is what I am looking for.

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.