Power BI desktop, help with metrics

[Ning]

HI,

I am trying to create a percentage based metric that divides a "task per hour" with defined "BE","ME" and "EE" numbers.

The "task per hour" is a measure with the sum of Tasks divided by sum of total time. The number I get here is working fine and does not need changing.

The BE, ME and EE numbers are all columns.

I cant divide the task per hour with the BE,ME and EE numbers since it doesnt let you divide non measures.

My BE, ME and EE data table looks like below:

I tried doing task per hour divided by average or sum of BE,ME and EE and it does not give you the correct results, it only gives you the correct results when you only have that one particular task highlighted (see below).

Sorry if my explanation is not clear, I essentially just need the results to look like the right table without needing to filter anything.

I will elaborate more if needed.

Thank you,

[Ning]

@v-xjiin-msft@MFelix

HI!!

I was messing around with the tables and I was able to figure it out! for some reason my BE ME and EE metrics had multiple iterations in the table (no idea why). I noticed it when I added something else in and for some reason it duplicated like crazy with different numbers (again no idea why). I added in an additional filter to ignore those iterations and I was able to get the division to work correctly.

Thank you for the help.

[Ning]

@v-xjiin-msft@MFelix

Task_count is a column

task per hour is a calculated column "Task Per Hour = SUM(Data[Task_Count])/(SUM(Data[Task_time])*24)"

KPI is a calculated column (this is the one I cant get working)

Below is some sample data of what was used.

The first table is a "Table" visualization with the following values

-Task

-Task_count

-Task per hour

-KPI

-Expectation (Dont worry about this one)

The table is filtered at a range with Create_date.

In the already posted table, when no other filters are applied, the results I got for 911 audits were

Task count: 14

Task per hour: 14:00

KPI 64.99%

expectact result is

Task count: 14

Task per hour: 14:00

KPI 84.85%

The 84.85% should be gotten from the task per hour (14:00) divided by the tasks ME number (16.50).

14/16.50 = 84.85% 

I hope that makes more sense.

Thank you,

[v-xjiin-msft]

Hi @Ning,

=> I tried doing task per hour divided by average or sum of BE,ME and EE.

In your last two screenshots, what is the corresponding column for your requirement? KPI? 

In your shared sample data, there's no Task per Hour. Is it a value which was calculated? If it is just a regular column, please also share us some sample data.

And since you have already shared us the sample data, please try to give us your desired result based on your sample data. So that we can get a right direction and make some test to find a solution.

Thanks,
Xi Jin.

[MFelix]

Hi @Ning,

Not reaaly understood wha are the calculations you want can you please show better the way you want the calculations.

Picking up the 911 Audits where does all the number come from:

Count = 14 

Task per hour = 14

KPI = 84,85%

Please elaborate on where to get all the numbers for this so I can help you better.

Regards,

MFelix