How do i share a value based on spending time

[ramie]

Hi power bi family 

Here i have a new problem which i am trying to solve in different ways lik e creating measure and calculated column  but not getting expected output.

Problem is, in the below image i need to share margin value with the perons who are working on top of it using WO_ID , here is 2 persons but in some cases more persons are involved . but here 2 persons are spending certain time and based on the time i need to share margin value to them i know the formula ( formula is ((spending time / total time )* value)) i am applying this in both ways like creating measure and calculated column as well but not getting correct OP.

here i am aware the way i written the formula is not correct . in the above image margin value is availabe in measure(i created margin values as a measure) level and time is available in column level (different table) in decimal type .

please you just try in your calculator and apply this manually ((spending time / total time )* value) you will get value approximately . but if i apply in measure it is not comming.

so can any please help me solution and suggestions 

 Thanks in Advance

[v-yalanwu-msft · Accepted solution]

Hi, @ramie ;

Try it.

Measure = SUM([DURATION])/CALCULATE(SUM([DURATION]),ALL('Table') )*CALCULATE(SUM([Value]),FILTER(ALL('Table'),[UOWNER]=MAX('Table'[UOWNER])))

the final show:

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ramie]

Hi @v-yalanwu-msft 

i know long time ago you replied but rsults are not matching.

actually above measure is working fine as  what i expected but , i want to modify other  way , i tried  to modify it but i am unable to do it . i have choosen 1 month date then 4 wo codes  has been fetched and their related values , but when i select 1 wo code values getting correct but , if i  choose 2 work orders(wo code) result is not correct . The formula taking total time and diving by each one involved in the  work . But here a person is not involved in the work , but the total time divide his spent time by total time based on selection which gives  me incorrect shared values.

you see below it is  showing correct because of single seelction

can you see here result is different because of multiple selection and  the person i highlighted  , he is not involved in other wo code. the need is  total time  should be work order only not more that that , if he spent time in other wo codes, then will consider ,can u please suggest me how can i add do modification

 @v-yalanwu-msft  pelase help me in this ant modifcations or any suggestions

Looking forward to your response

Thanks

[ramie]

Hi @v-yalanwu-msft 

its amazing , you understand the logic and giving me the absolute formula and it works perfectly ,

can you please give me a suggestions to learn advanced dax like you to solve this kind of problem .

how do i estimate a problem , you know i struguled  more than 10 days , but you did it shortly. can u please guide me for feature problems to solve easily to my own and i need to buid a big formulas like u did.

HELP ME  GUIDE ME

Thank you so much @v-yalanwu-msft 

Thanks again

[amitchandak]

@ramie , I do not see the required column in your tables.

remember multiplication should done at line level 

means Sumx(Table, [A]*[B])

But division should be done post aggregation means sum([A])/ Sum([B])

Keeping these things in mind we have to create a measure. Now if need that needs multiplication  post aggregation, we need to force values or summarize to the formula (grouping)

refer if needed

https://www.youtube.com/watch?v=ufHOOLdi_jk