Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have two example tableas as below
Combined
Location | MONTH | YEAR | Seater | Date | Total Spend | UniqueID |
A | JAN | 2020 | 4S | 01-Jan-20 | 2022012 | 4S-A-January-2020 |
B | FEB | 2020 | 4S | 01-Jan-20 | 1734348 | 4S-B-January-2020 |
C | MAR | 2020 | 4S | 01-Jan-20 | 1974497 | 4S-C-January-2020 |
A | JAN | 2020 | 6S | 01-Jan-20 | 1840699 | 6S-A-January-2020 |
Last_3M_data
Location | Date | Seater | Users | UniqueID | Team |
A | 01-Jan-20 | 4S | A001 | 4S-A-January-2020 | Alpha |
A | 02-Jan-20 | 4S | A001 | 4S-A-January-2020 | Alpha |
B | 01-Jan-20 | 4S | A002 | 4S-B-January-2020 | Bravo |
C | 01-Jan-20 | 4S | A003 | 4S-C-January-2020 | Delta |
A | 03-Jan-20 | 6S | A001 | 6S-A-January-2020 | Alpha |
Both the tables are linked together with the UniqueID
I am trying to create a measure which gives me an "average cost per user" irrespective of team
Ideally its calculated as below
Total Spend/ No of users(not unique)
Once I have that number I would like to multiply the "average cost per user" * number of users in a team to get the amount spent by the team from the Total spend.
That being said I've to create different sheets for different teams ideally I put the team under filter and select a team and calculate the below measures
1. Need to get the total number of users irrespective of which team they are part of
3.
@Anonymous , Make sure you create common dimensions and join them with both tables
This can be done on one the common dimension that is base of this calculation
sumx(summarize(location,location[Location],"_1",CALCULATE(COUNT(Last_3M_data[Emp Id]),"_2",[Avg_Cost_per-emp_ per-trip])),[_1]*[_2])
Thank you for responding. But I dont understand your solution, Please pardon me am very new to Power BI and DAX.
If you are referring to linked columns then UniqueID is the one from those two tables which are linked with each other in Models.
Could you please explain how exactly the solution you've given would work.
Thank
Hi @Anonymous,
Could you share the sample pbix via cloud service like onedrive for business?So that can help us know your scenario more clear.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
101 | |
90 | |
85 | |
74 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |