Measure to divide a total based on the count of another string column

[TG23__]

Hi, I'm looking for the syntax for this measure I want to create for my visual. I have tasks for a project the have associated effort hours and a resource assigment. When a task shares multiple resource, I want the total effort hours for that task to be divided by the number of resources assigned. Right now the table shows each resource all having the same amount of effort hours regardless if mulitple people are assigned to a task. Any ideas?

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

effort total measure: =
SUMX (
    VALUES ( data[task_name] ),
    CALCULATE (
        DIVIDE ( SUM ( data[effort] ), COUNTROWS ( data ) ),
        ALL ( data[resource] )
    )
)

[TG23__]

Thanks for your help on this! but I want my new measure to divide the orignal effort hours by the number of resources for that task. For example, William and Dalton both are assigned a 12 hour task called "Onsite Day 1" because they are both on the same task. Their new effort hours should equal 6. (two people on a task that takes 12 hours should result in a 6 hour task)

[Jihwan_Kim]

Hi,

I am not 100% sure if I understood your question correctly, but please check the below picture and the attached pbix file.

[TG23__]

This is the correct result but it is not working on my end. Perhaps because "resources" and "project name" are columns from different tables. "Task name" , "Effort" , and "Finish date" are from one table and the other columns are not. Is there a solution for this

[Jihwan_Kim]

Hi, thank you for your message, and please share your sample pbix file's link here, and then I can try to look into it.

Thank you.

[TG23__]

https://drive.google.com/file/d/19hisWp8BmSFyq2A4Wg66DOQ1H81woeuQ/view?usp=sharing

Here is the link to my sample data. 
I am looking for a resources' effort hours to be divided equally among the number of resources assigned to the same task on the same project. 

[Jihwan_Kim · Accepted solution]

Hi,

I am not 100% sure but please check the below picture and the attached pbix file if it suits your requirement.

[TG23__]

This worked! Tysm

[v-kaiyue-msft]

Hi @TG23__ ,

@foodd , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution: 

Create measure.

MEASURE = 
VAR _sum_effort =
    CALCULATE (
        SUM ( 'Table'[Effort] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Task Name] = MAX ( 'Table'[Task Name] ) )
    )
VAR _count_resourse =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Resourse] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Task Name] = MAX ( 'Table'[Task Name] ) )
    )
RETURN
    DIVIDE ( _sum_effort, _count_resourse )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TG23__]

Thanks for your help on this! but I want my new measure to divide the orignal effort hours by the number of resources for that task. For example, William and Dalton both are assigned a 12 hour task called "Onsite Day 1" because they are both on the same task. Their new effort hours should equal 6. (two people on a task that takes 12 hours should result in a 6 hour task

[v-kaiyue-msft]

Hi @TG23__ ,

Modify measure.

MEASURE = 
VAR _count_resourse =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Resourse] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Task Name] = MAX ( 'Table'[Task Name] ) )
    )
RETURN
    DIVIDE ( MAX('Table'[Effort]), _count_resourse )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TG23__]

This is the correct result but it is not working on my end. Perhaps because "resources" and "project name" are columns from different tables. "Task name" , "Effort" , and "Finish date" are from one table and the other columns are not. Is there a solution for this?

[TG23__]

Here is my sample data: https://drive.google.com/file/d/19hisWp8BmSFyq2A4Wg66DOQ1H81woeuQ/view?usp=drive_link

[foodd]

Hello @TG23__ , and thank you for sharing a question with the Community.   This reply is informational. Please follow the decorum of the Community Forum when asking a question.

Please share your work-in-progress Power BI Desktop file (with sensitive information removed) and any source files in Excel format that fully address your issue or question in a usable format (not as a screenshot). You can upload these files to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then provide the file's URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.  Proud to be a Super User!