Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Does anyone know how to do it??? Thank you in advance.
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
and here comes second table:
Date | Date EDW | Customer | Rev | COS | BA | Order | Customer pther name |
04-05-20 | 202007 | abradab | 300 | 22 | PQER | 1 | aaa |
04-05-20 | 202007 | abradab | 22 | 33 | PQER | 1 | aa |
04-05-20 | 202007 | abradab | 33 | 21 | PQER | 2 | aa |
04-05-20 | 202007 | abradab | 1 | 33 | PQER | 3 | sss |
04-05-20 | 202007 | abradab | 1111 | 3444 | PQER | 3 | ss |
04-05-20 | 202007 | abradab | 2222 | 7 | PQER | 4 | d |
04-05-20 | 202007 | bladarab | 3444 | 7 | PQER | 5 | ddd |
04-05-20 | 202007 | bladarab | 45465 | 7 | PQER | 5 | dd |
04-05-20 | 202007 | bladarab | 68687 | 44 | PQER | 7 | dd |
04-05-20 | 202007 | bladarab | 7975 | 5 | PQER | 765 | d |
04-05-20 | 202007 | bladarab | 245565 | 757 | PQER | 8798 | zet |
@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:
Date | Date EDW | RTM | Type | BA | Amount |
04-05-20 | 202007 | Direct | Hedge | PQTR | 100 |
04-05-20 | 202007 | Direct | Hedge | PQEA | 200 |
04-05-20 | 202007 | Direct | Hedge | PQEB | 300 |
04-05-20 | 202007 | Direct | Hedge | PWEC | 400 |
04-05-20 | 202007 | Direct | Hedge | PQED | 500 |
04-05-20 | 202007 | Direct | Hedge | PQEF | 600 |
04-05-20 | 202007 | Direct | Hedge | PQER | 700 |
04-05-20 | 202007 | Direct | Hedge | PIUY | 800 |
04-05-20 | 202008 | Direct | Hedge | PQTR | 900 |
04-05-20 | 202008 | Direct | Hedge | PQEA | 1000 |
04-05-20 | 202008 | Direct | Hedge | PQEB | 1100 |
04-05-20 | 202008 | Direct | Hedge | PWEC | 1200 |
04-05-20 | 202008 | Direct | Hedge | PQED | 1300 |
04-05-20 | 202008 | Direct | Hedge | PQEF | 1400 |
04-05-20 | 202008 | Direct | Hedge | PQER | 1500 |
04-05-20 | 202008 | Direct | Hedge | PIUY | 1600 |
04-05-20 | 202009 | Direct | Hedge | PQTR | 1700 |
04-05-20 | 202009 | Direct | Hedge | PQEA | 1800 |
04-05-20 | 202009 | Direct | Hedge | PQEB | 1900 |
04-05-20 | 202009 | Direct | Hedge | PWEC | 2000 |
04-05-20 | 202009 | Direct | Hedge | PQED | 2100 |
04-05-20 | 202009 | Direct | Hedge | PQEF | 2200 |
04-05-20 | 202009 | Direct | Hedge | PQER | 2300 |
04-05-20 | 202009 | Direct | Hedge | PIUY | 2400 |
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-20 | 202007 | Direct | Hedge | PQER | 700 |
Text attached I hope this is what you have expected?
Date | Date EDW | RTM | Type | BA | Amount |
04-05-20 | 202007 | Direct | Hedge | PQER | 100 |
04-05-20 | 202007 | Direct | Hedge | PQEA | 200 |
04-05-20 | 202007 | Direct | Hedge | PQEB | 300 |
04-05-20 | 202007 | Direct | Hedge | PWEC | 400 |
04-05-20 | 202007 | Direct | Hedge | PQED | 500 |
04-05-20 | 202007 | Direct | Hedge | PQEF | 600 |
04-05-20 | 202007 | Direct | Hedge | PQTR | 700 |
04-05-20 | 202007 | Direct | Hedge | PIUY | 800 |
04-05-20 | 202008 | Direct | Hedge | PQER | 900 |
04-05-20 | 202008 | Direct | Hedge | PQEA | 1000 |
04-05-20 | 202008 | Direct | Hedge | PQEB | 1100 |
04-05-20 | 202008 | Direct | Hedge | PWEC | 1200 |
04-05-20 | 202008 | Direct | Hedge | PQED | 1300 |
04-05-20 | 202008 | Direct | Hedge | PQEF | 1400 |
04-05-20 | 202008 | Direct | Hedge | PQTR | 1500 |
04-05-20 | 202008 | Direct | Hedge | PIUY | 1600 |
04-05-20 | 202009 | Direct | Hedge | PQER | 1700 |
04-05-20 | 202009 | Direct | Hedge | PQEA | 1800 |
04-05-20 | 202009 | Direct | Hedge | PQEB | 1900 |
04-05-20 | 202009 | Direct | Hedge | PWEC | 2000 |
04-05-20 | 202009 | Direct | Hedge | PQED | 2100 |
04-05-20 | 202009 | Direct | Hedge | PQEF | 2200 |
04-05-20 | 202009 | Direct | Hedge | PQTR | 2300 |
04-05-20 | 202009 | Direct | Hedge | PIUY | 2400 |
@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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |