Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Everyone,
Earlier today I posted a query DAX - How to use a Column Value to use in a Measure and received a great solution from @parry2k. I would appreciate help with the next step in my PowerBi report.
I now have a table2 with the below.
Table.Date | Table.Salesperson | Table.$ Amount | Measure.Total | Measure.Day % |
1st Feb | Bob | 200 | 1000 | 20% |
1st Feb | Jessica | 200 | 1200 | 17% |
2nd Feb | Bob | 300 | 1000 | 30% |
2nd Feb | Jessica | 400 | 1200 | 33% |
3rd Feb | Bob | 500 | 1000 | 50% |
3rd Feb | Jessica | 600 | 1200 | 50% |
And another table with the below (I used a simular formula for the Total as per the above table). I've joined the tables by a 'KeyTable' which has two entries - Bob and Jessica.
Table.Date | Table.Salesperson | Table.Costs | Measure.TotalCosts |
1st Feb | Bob | 20 | 60 |
1st Feb | Jessica | 30 | 90 |
2nd Feb | Bob | 20 | 60 |
2nd Feb | Jessica | 30 | 90 |
3rd Feb | Bob | 20 | 60 |
3rd Feb | Jessica | 30 | 90 |
What I would like to do is link the two tables, by Salesperson, and multiply the Measure.Day% from the first table with Measure.Total in the second table, so I get the below.
Table.Date | Table.Salesperson | Table.$ Amount | Measure.Total | Measure.Day % | Table2.TotalCosts | Day% x Total Costs (Help) |
1st Feb | Bob | 200 | 1000 | 20% | 60 | 12 |
1st Feb | Jessica | 200 | 1200 | 17% | 90 | 15 |
2nd Feb | Bob | 300 | 1000 | 30% | 60 | 18 |
2nd Feb | Jessica | 400 | 1200 | 33% | 90 | 30 |
3rd Feb | Bob | 500 | 1000 | 50% | 60 | 30 |
3rd Feb | Jessica | 600 | 1200 | 50% | 90 | 45 |
At the moment I have the 'TotalCosts' coming back - but it's a sum of the Costs in Table2, rather then those Bob costs and Jessica costs
Table.Date | Table.Salesperson | Table.$ Amount | Measure.Total | Measure.Day % | Table2.TotalCosts | Day% x Total Costs (Help) |
1st Feb | Bob | 200 | 1000 | 20% | 150 | 30 |
1st Feb | Jessica | 200 | 1200 | 17% | 150 | 25 |
2nd Feb | Bob | 300 | 1000 | 30% | 150 | 45 |
2nd Feb | Jessica | 400 | 1200 | 33% | 150 | 50 |
3rd Feb | Bob | 500 | 1000 | 50% | 150 | 75 |
3rd Feb | Jessica | 600 | 1200 | 50% | 150 | 75 |
I've be grateful for advice anyone can offer. Let me know if any more info or the queries I have so far would be useful.
Thanks
Andy
Solved! Go to Solution.
I've managed to solve this one myself. I changed the join type to be "Cross Filter Direction" = Both (instead of Single) and that gave me what I was looking for.
Thanks
I've managed to solve this one myself. I changed the join type to be "Cross Filter Direction" = Both (instead of Single) and that gave me what I was looking for.
Thanks
User | Count |
---|---|
40 | |
26 | |
22 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |