Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
I'm sorry for the somewhat strange title. I don't know how to call the issue that i have.
Let me explain, i have 3 tables which look like this:
Table 1 - M
Columns:
Date/time of creation
Record number
Table 2 - A
Columns:
Date/time of creation
Record number
Table 3 - F
Columns:
Month (e.g. March 2018)
Number of employees
What do i want to achieve
I already have calculated the number of creations per month in two ways:
Via a calculated column:
MonthCreation = MONTH(Date/Time of Creation)
When i put the above in a bar chart where MonthCreation is on the Axis and the Count of Record numbers is the value. It works.
But i want the number of employees as a line plotted as well in the same graph. My end-game here is dividing the total number of records (from table M & F) against the number of employees. Example: March2018 created 2000 records and i have 2 employees, which results in 1000 records per employee in March.
Can you guys help me out in how i can achieve this?
Solved! Go to Solution.
Hi,
Create a column in Table M and A as follow:
Month Name = FORMAT([Date/Time of Creation], "MMM") & " " & Year([Date/Time of Creation])
to ensure you have similar formats for Month in all three tables
then create a relationship between all three tables using Month column.
once you created relationship you should create following measure
Records/Employees = Divide((Count('table 1'[Record Number])+Count('table 2'[Record Number])),Sum('table 3'[Number of employees],0)
let me know if it works.
Thank you
Hi,
Create a column in Table M and A as follow:
Month Name = FORMAT([Date/Time of Creation], "MMM") & " " & Year([Date/Time of Creation])
to ensure you have similar formats for Month in all three tables
then create a relationship between all three tables using Month column.
once you created relationship you should create following measure
Records/Employees = Divide((Count('table 1'[Record Number])+Count('table 2'[Record Number])),Sum('table 3'[Number of employees],0)
let me know if it works.
Thank you
Hi,
Thanks for your help. it works!
You are a hero 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |