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,
I have 2 tables. Table A shows the number of hours eligible to work and Table B shows how many hours they actually worked. I need to calculate Time% i.e. (hours/Time)*100 for each resource for everyday as output. I have many resources in the table. Can anyone help to achieve this in DAX. I have highlighted the expected output column.
Table A | Table B | |||||||
Resource | Date | Time | Resource | Date | hours | Output | ||
A | 01/01/2022 | 7.5 | A | 01/01/2022 | 7.5 | 100 | ||
A | 02/01/2022 | 7.5 | A | 02/01/2022 | 4 | 53.33333 | ||
A | 03/01/2022 | 7.5 | A | 03/01/2022 | 3.5 | 46.66667 | ||
A | 04/01/2022 | 7.5 | A | 04/01/2022 | 7 | 93.33333 | ||
A | 05/01/2022 | 7.5 | A | 05/01/2022 | 7.5 | 100 | ||
A | 06/01/2022 | 7.5 | A | 06/01/2022 | 7.5 | 100 | ||
B | 07/01/2022 | 7.5 | B | 07/01/2022 | 6 | 80 | ||
B | 08/01/2022 | 7.5 | B | 08/01/2022 | 7.5 | 100 | ||
B | 09/01/2022 | 7.5 | B | 09/01/2022 | 7.5 | 100 | ||
B | 10/01/2022 | 7.5 | B | 10/01/2022 | 7.5 | 100 |
Solved! Go to Solution.
Hi @Saranya_Tamil ,
You can try the below formula in calculated column,
Output Column =
VAR _lookupvalue = LOOKUPVALUE('Table A'[Time],'Table A'[Resource],'Table B'[Resource])
VAR _output = DIVIDE('Table B'[hours],_lookupvalue,0)*100
RETURN _output
Thanks,
Hi @Saranya_Tamil ,
You can try the below formula in calculated column,
Output Column =
VAR _lookupvalue = LOOKUPVALUE('Table A'[Time],'Table A'[Resource],'Table B'[Resource])
VAR _output = DIVIDE('Table B'[hours],_lookupvalue,0)*100
RETURN _output
Thanks,
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 |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |