topic Re: Measure to divide a total based on the count of another string column in DAX Commands and Tips

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

TG23__ — Mon, 08 Jul 2024 18:46:11 GMT

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?

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

foodd — Mon, 08 Jul 2024 18:55:47 GMT

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-Forum/ba-p/963216

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/1447523/highlight/true#M607150

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!

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

Anonymous — Tue, 09 Jul 2024 02:49:24 GMT

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.

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

Jihwan_Kim — Tue, 09 Jul 2024 02:50:23 GMT

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] ) ) )

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

TG23__ — Tue, 09 Jul 2024 14:04:26 GMT

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)

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

TG23__ — Tue, 09 Jul 2024 14:04:49 GMT

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

Anonymous — Wed, 10 Jul 2024 03:01:02 GMT

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.

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

Jihwan_Kim — Wed, 10 Jul 2024 03:11:29 GMT

Hi,

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

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

TG23__ — Wed, 10 Jul 2024 17:10:22 GMT

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?

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

TG23__ — Wed, 10 Jul 2024 17:15:37 GMT

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

Jihwan_Kim — Thu, 11 Jul 2024 02:43:18 GMT

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.

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

TG23__ — Thu, 11 Jul 2024 13:42:17 GMT

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.

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

TG23__ — Thu, 11 Jul 2024 13:47:40 GMT

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

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

Jihwan_Kim — Fri, 12 Jul 2024 05:16:32 GMT

Hi,

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

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

TG23__ — Fri, 12 Jul 2024 19:01:04 GMT

This worked! Tysm