Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello to all
I'm trying to display the % for each Activity in the graphic displayed below.
In my case I have the days work in one table and the list of activities in other. They are linked by an id.
In this case I wanna display 28,81/40 %. I'm trying to use a measure but the value is not correct.
What am I missing?
Days worked are the Value
Activities are the Legend.
I do no have any calculation for each activity.
Thanks
Solved! Go to Solution.
Hi @Anonymous,
If I understand corrcly you are filtering your worklog by a slicer/filter try to adapt your formula to this
Working days % per week = Var Total_Week_Days = CALCULATE ( SUM( worklog[Days Worked] ); FILTER ( ALLSELECTED ( worklog ); worklog[Week] <= MAX ( worklog[Week] ) ); VALUES ( worklog[Week] ) ) return SUM(worklog[Days Worked])/Total_Week_Days
Instead of ALL I use ALLSELECTED that refers to the table filter/sliced.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsRegards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello
Thanks @MFelix but in fact it is more important to show the other way around. I tried your approach but it was not very good for our dashboard viewers. 🙂
HI @Anonymous,
You can add the 40% in the tooltip not in the data labels. Is this value a measure or direct calculation in your table?
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsIn fact is a column.
I managed to make it appear in the tool tip by using a %GT. But If I had more weeks, it calculates to all those weeks (expectable) but it was not what I want. I want the % by each week and activity.
Maybe I have to change something in the data. But not figuring out what.
The days worked are just a simple conversion of seconds to days, there are several entries for each day/week by person.
There is a relationishp beetween the days and the sub-activity which is in another table that has the main activity.. Maybe I have to do a measure to add the time by each sub-activity, and use that. But I tried that approach and failed.
Can you share data to make some tests?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsSee if the image helps:
Above we have the table that contains the time logged by user and the sub-activity. This sub-activity is under a project (or Activity)
in the visual I Have:
Week in Axis
Legend Activities
Value the days worked
Tooltip %GT Days Worked
Hi @Anonymous,
I have made a simplified model of you data in PBI working with the columns you needed to your chart I have come up to the result below.
Create the following measure in the Time_Log table:
Working days % per week = Var Total_Week_Days = CALCULATE ( SUM( Time_Log[DaysWorked] ); FILTER ( ALL ( Time_Log ); Time_Log[Week] <= MAX ( Time_Log[Week] ) ); VALUES ( Time_Log[Week] ) ) return SUM(Time_Log[DaysWorked])/Total_Week_Days
The variable in the measure returns the total number of days worked and that's what I use to calculate the percentage per week. In the print screen you can check the result for comparason of the measure above and the Grand Total I first told you.
As you can see the first matrix (red highlight) as a total of 100% in the table, the second one using the measure (green highligth) as a total of 100% per week (column) that is what I believe you want.
If you want the total number of days use only the var Total_Week_Days as your measure.
Please also check the table names.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelixthanks for the quick reply.
I used your measure but it gives me a very low numbers like 0,01.
I think i'm doing something wrong.
Maybe is adding values all instead of values per week.
Forgot to mention you need to format the measure as a %.
Sorry.
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix, Just did that but I get something like 1.96%, 1.8% 😞
If I use just the "calculate" part I get something 1279766. which is very high, but in your sample is correct. 😞
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsWorking days % per week = Var Total_Week_Days = CALCULATE ( SUM( worklog[Days Worked] ); FILTER ( ALL ( worklog ); worklog[Week] <= MAX ( worklog[Week] ) ); VALUES ( worklog[Week] ) ) return SUM(worklog[Days Worked])/Total_Week_Days
This is what I did.
In fact after seen you reply I got back to the calculation and saw that I was using a column and not a measure so now I see the correct value in the "Calculate" part.
Please note that:
Days Worked = worklog[timeworked]/3600/8 to convert to "days of work".
Sorry but your message was dual, were you able to make it work as a measure?
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelixsorry for the confusion.
If I put the Calculate part of you measure formula I get the value that you can see in the image:
Anf that value is not correct, maybe because I'm filtering by another entry, it seems the measure shows the total value? I'm filtering by team using a correlation between author and the team. In the data I showed earlier all people info go to that table.
Hi @Anonymous,
If I understand corrcly you are filtering your worklog by a slicer/filter try to adapt your formula to this
Working days % per week = Var Total_Week_Days = CALCULATE ( SUM( worklog[Days Worked] ); FILTER ( ALLSELECTED ( worklog ); worklog[Week] <= MAX ( worklog[Week] ) ); VALUES ( worklog[Week] ) ) return SUM(worklog[Days Worked])/Total_Week_Days
Instead of ALL I use ALLSELECTED that refers to the table filter/sliced.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelixPerfect!! I manage to do it by using an ALLEXCEPT, but your version is cleaner and more flexivel.
Many Thanks
Great news
Please mark the answer so other can be helped.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |