Forum Discussion
How do i share a value based on spending time
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
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.
4 Replies
v-yalanwu-msftCommunity Support
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.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
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
- amitchandak
Super User
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
