Table Report Measure Issue

[Shaun_Fourie]

Hi! So I have two tables in my Power BI report, see below tables. (Below are snippets from Excel, as I do not want to share the actual table data from Power Bi).

Tasks Table

The below table houses all the Tasks with unique Task Ids, Task Type, Customer and Summary.

Time Logs Table
The below table houses all the time logs for each task with unique Log Ids, Task Id, Time Logged in hours.

What I would like to do is: 

1. Sum up the total time logged and time logged per customer - Done ✔️

2. Calculate the % distribution of time logged per customer - Done ✔️

3. Calculate the total time logged for ongoing tasks - Done ✔️

4. For each customer, calculate the total time logged for customer (excluding ongoing task time logged) + (% distribution * total time logged for ongoing tasks), irrespective that ongoing tasks are allocated to the Blue customer. - Issue ❌

My desired output would be a table report called Final Time Logged, Final Time Logged = total time logged for customer (excluding ongoing task time) + (% distribution * total time logged for ongoing tasks)

I am having an issue with point 4. I can easily do it through excel (above example) but through Power BI my measure is not allowing me to apply the time spent on ongoing tasks to all customers, as the task has the Blue customer allocated to it. I am unsure how to resolve this. Any suggestions?

[anmolmalviya05]

Hi @Shaun_Fourie , Please try the below measure:

FinalTimeLogged =
VAR TotalTimeExcludingOngoing =
CALCULATE(
SUM('Time Logs Table'[Time Logged]),
'Tasks Table'[Task Type] <> "Ongoing"
)

VAR TotalTimeOngoing =
CALCULATE(
SUM('Time Logs Table'[Time Logged]),
'Tasks Table'[Task Type] = "Ongoing"
)

VAR PercentageDistribution =
DIVIDE(
SUM('Time Logs Table'[Time Logged]),
CALCULATE(SUM('Time Logs Table'[Time Logged]), ALL('Tasks Table'))
)

VAR DistributedOngoingTime =
PercentageDistribution * TotalTimeOngoing

RETURN
TotalTimeExcludingOngoing + DistributedOngoingTime

If this post helps to answer your question, please consider accepting it as a solution so others can find it more quickly when they face a similar challenge.

Proud to be a Microsoft Fabric community super user

Let's Connect on LinkedIn

Subscribe to my YouTube channel for Microsoft Fabric and Power BI updates.

[Shaun_Fourie]

Hi @anmolmalviya05 ! Thank you for the timely response.

I have replicated your suggestion and have applied it to my report, but I am still unable to allocate the time to the other customers. Please see snippet below of my actual Power Bi table report, where customer Blue = BancX.

Notice that the FinalLoggedTime is the same as the TotalTimeLoggedExcludingOngoing for the other customers, when it should have increased like it did for BancX.

[bhanu_gautam · Accepted solution]

@Shaun_Fourie , Try using this measure

DAX
TotalTimeLoggedExcludingOngoing =
CALCULATE(
SUM('Time Logs Table'[Time Logged]),
'Tasks Table'[Task Type] <> "Ongoing"
)

TotalTimeLoggedOngoing =
CALCULATE(
SUM('Time Logs Table'[Time Logged]),
'Tasks Table'[Task Type] = "Ongoing"
)

PercentageDistribution =
DIVIDE(
SUM('Time Logs Table'[Time Logged]),
CALCULATE(SUM('Time Logs Table'[Time Logged]), ALL('Tasks Table'))
)

DistributedOngoingTime =
[PercentageDistribution] * [TotalTimeLoggedOngoing]

FinalTimeLogged =
[TotalTimeLoggedExcludingOngoing] + [DistributedOngoingTime]

[Shaun_Fourie]

Hi @bhanu_gautam! Thank you for the timely response.

I have replicated your suggestion and have applied it to my report, but I am still unable to allocate the time to the other customers. Please see snippet below of my actual Power Bi table report, where customer Blue = BancX. Notice that the FinalLoggedTime is the same as the TotalTimeLoggedExcludingOngoing for the other customers, when it should have increased like it did for BancX.