Forum Discussion
Percentage from Two Columns
Forgive me, I am trying to work through the design process. I have designed a Matrix based on a single table of data, with a few relationships. The raw data has the following look:
| Staff Name | Billed Hours | Appointment Hours | Other Data | Other Data |
| Jane Doe | 0 | 1 | ||
| John Doe | 1 | 1 | ||
| Jane Doe | 2 | 3 | ||
| John Doe | 0 | 3 |
Based on this data, I made a table (and I have tried a matrix also), that looked like the following:
| Staff Name | Billed Hours | Appointment Hours | Percentage Billed |
| Jane Doe | 2 | 4 | 100% |
| John Doe | 1 | 4 | 100% |
No matter what I do, I get 100%. I have looked at a variety of different resolutions, but I cannot seem to find the correct one for me. Would someone be able to help assist?
3 Replies
andersona1983 , Create a New Measure
Measure =
divide(sum(Table[Billed Hours]),sum(Table[Appointment]))
amitchandak I have tried this and it does not work. I pull the data into Excel and do a quick sumif for the staff member, and I get the same numbers in column Billed Hours and Appointment Hours. When I run a DAX formula using your example, i get the following:
Billed: 705
Appointment: 1890
Percentage: 24.14 (Except it shoudl be 37.3)
try
Hours Percentage Billed = DIVIDE( CALCULATE(SUM(Table[Billed Hours]), ALLEXCEPT(Table, Table[Staff Name]) ), CALCULATE(SUM(Table[Appointment Hours]), ALLEXCEPT(Table, Table[Staff Name]) ) )
