Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
TG23__
Frequent Visitor

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

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?
Screenshot 2024-07-08 144125.png

1 ACCEPTED SOLUTION

Hi,

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

 

Jihwan_Kim_0-1720761372080.png

 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


View solution in original post

14 REPLIES 14
Jihwan_Kim
Super User
Super User

Hi,

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

 

Jihwan_Kim_0-1720493373149.png

 

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


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)

Hi,

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

 

Jihwan_Kim_0-1720581065261.png

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


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

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


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. 

Hi,

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

 

Jihwan_Kim_0-1720761372080.png

 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


This worked! Tysm

v-kaiyue-msft
Community Support
Community Support

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 )

vkaiyuemsft_0-1720493327972.png

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.

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

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 )

vkaiyuemsft_0-1720580437881.png

 

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.

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__
Frequent Visitor
foodd
Super User
Super User

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.