Add Preset Percentage in Power BI Visual

Question

I have a log of worker time spent throughout a workday, and have a visual that shows how their time is spent as a percentage of their day. However, the workers do not log "allowance" times, such as 10% for pre-job planning, and 5% for admin (discussions with supervisor, timesheets, etc). Is there a way to include these percentages without going back and "creating" data?

Thank you!

Sample data

Sample visualization

Dan Dan · Accepted Answer · 2020-01-24T09:52:44

Yes, but you'd need to modify your data model slightly.

Start by creating a new table that will hold all the values of the [Activity] column, along with the 2 new "virtual" items for which you don't have any actual data:

Activity = UNION(DISTINCT(Timesheet[Activity]), {"Pre-job planning", "Admin"})

Here, I'm assuming that the table we see in your screenshot is called Timesheet.

Then, create a relationship between the new Activity table and the Timesheet[Activity]-column on your TimeSheet table. Hide the [Activity] column on your Timesheet table. Modify your visuals so that they use the new Activity table instead for slicing the data.

So far, you won't see any difference.

Now you create a new measure [Total Hours] using the following DAX:

Total Hours =
SUMX(
    VALUES(Activity[Activity]),
    SWITCH(Activity[Activity],
        "Pre-job planning", SUM(Timesheet[Hours]) * 0.1,
        "Admin", SUM(Timesheet[Hours]) * 0.05,
        CALCULATE(SUM(Timesheet[Hours]))
    )
)

Use this measure in all your visuals, instead of the [Hours] column, and things should magically work. Even the total should correctly be increased by 15 %.

Add Preset Percentage in Power BI Visual

1 Answers