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.
Hey,
Suppose I have a table called Hires as:
ID NAME
1 A
2 B
3 C
.
.
And another Table Called Employees
ID Country
1 X
2 Y
3 Z
So I want to be able to calculate the hiring rate based on the two tables which would be count of ID in Hires table divided by count of ID's in the employee table. Is there a way that I can create a measure to calculate the hiring rate based on the ID's from the 2 tables?
Solved! Go to Solution.
I think this can be done fairly easily.
Just use "new measure" and use DISTINCTCOUNT('Hires'[ID])/(DISTINCTCOUNT('Hires'[ID])+DISTINCTCOUTN('Employees'[ID])). You may then want to format the measure as "%".
Please try it out and let us know if this works as I have not tested it myself.
@Anonymous There are many ways, easiest and cleanest way is using variables and here is a screenshot of the same.
Hiring Rate = VAR EmpCount = CALCULATE(DISTINCTCOUNT(Employees[Country])) VAR HiresCount = CALCULATE(DISTINCTCOUNT(Hires[Country])) RETURN DIVIDE(HiresCount,EmpCount,0)
IS there any way we can calculate a measure based on 2 different tables? These 2 tables are not connected with each other directly
@Anonymous
Yep, measures are independent of tables so you can reference any number of tables you like, even if they don't have a relationship
Ya, you can use the "Related()" DAX function in some ways to do calculations.
Hey, can you please explain with an example.
Suppose I have a table called Hires as:
ID NAME
1 A
2 B
3 C
.
.
And another Table Called Employees
ID Country
1 X
2 Y
3 Z
So I want to be able to calculate the hiring rate based on the two tables which would be count of ID in Hires table divided by count of ID's in the employee table.
@Anonymous There are many ways, easiest and cleanest way is using variables and here is a screenshot of the same.
Hiring Rate = VAR EmpCount = CALCULATE(DISTINCTCOUNT(Employees[Country])) VAR HiresCount = CALCULATE(DISTINCTCOUNT(Hires[Country])) RETURN DIVIDE(HiresCount,EmpCount,0)
I think this can be done fairly easily.
Just use "new measure" and use DISTINCTCOUNT('Hires'[ID])/(DISTINCTCOUNT('Hires'[ID])+DISTINCTCOUTN('Employees'[ID])). You may then want to format the measure as "%".
Please try it out and let us know if this works as I have not tested it myself.
@Anonymous its diff to follow what you are referring to, can you please tag the appropriate people in your post? Also the denominator can be anything you want. I sent you a simple way to write the formula you can change it to what you want.
ya ok. thank you! @Anonymous
Hey,
Can you explain why we are adding ID of hires also in the denominator?
My mistake. If Employees is all-inclusive then you should not add it in the denominator.
@Anonymous
Wasn't that your question
"o I want to be able to calculate the hiring rate based on the two tables which would be count of ID in Hires table divided by count of ID's in the employee table. Is there a way that I can create a measure to calculate the hiring rate based on the ID's from the 2 tables? "
@Anonymous Hey I did not check your output while typing that. I was replying for the other solution.
I'll check this out and let you know!
@Anonymous if this is accepted solution, could you mark it as one.
Hey I am getting all 1 with this. The other solution gives me the correct answer. Thank You!
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |