cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## How to calculate the percentage of {Hours worked} from your top 10 customers over selected period

Hi all,

I have a question about a calculation that needs to calculate the hours worked for the top 10 customers. The top 10 needs to be based on 13 months, so 2022-06 till 2023-06. So over that period I need to know the sum of the hours that were worked for that customer. I visualize it in a matrix so for example, for period 2022-06 I can have 10 customers that were not the best customers for that exact period. But over all, they were the best customers over 13 months.

So I need to my 10 best customers over the 13 months, and for that customers I need to know the sum of hours for the period in my matrix.
I have a extra calendar table so by selecting 2023-06 as filter I get the 13 months I need. The visual in my picture is just filterd for the top 10 in visual level filter. But for my measure it needs to be calculated in a variable.

So variable 1 needs to be total amount of hours. So that is just my measure.

Variable 2 needs to be a variable that needs to calculate the amount of hours the Customers in my top 10 (over 13 months) made in the period in my matrix.

So I need to know how to calculate the percentage per month. I can remake the 2100 in my first column, that is just the whole total. But i need to know how many hours were made by my top 10 (over all the 13 months) in that period.

Who could help me out?

2 REPLIES 2
Community Support

Hello @TK12345 ,

Thank you for reaching out with your Power BI calculation question. You can follow these steps:

1. Create a measure, calculates the total hours worked for all customers over the 13-month period.

``````Total Hours =
SUM ( Table[HoursWorked] )
``````

1. Create a measure that identifies the top 10 customers based on the total hours worked over the 13-month period.

``````Top 10 Customers =
VAR TopCustomers =
TOPN ( 10, ALL ( Table[Customer] ), [Total Hours], DESC )
RETURN
CALCULATE ( [Total Hours], TopCustomers )
``````

1. Create a measure to calculate the total hours worked by the top 10 customers for each month displayed in the matrix.

``````Monthly Hours for Top 10 =
CALCULATE (
[Total Hours],
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Date] >= MIN ( 'Calendar'[Date] )
&& 'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
),
Table[Customer] IN VALUES ( 'Top 10 Customers'[Customer] )
)
``````

1. Create a measure to calculate the percentage of hours worked by the top 10 customers for each month.

``````Percentage of Hours for Top 10 =
DIVIDE ( [Monthly Hours for Top 10], [Total Hours], 0 )
``````

Thank you for your questions and support. If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Yifan Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Resolver I

```Monthly Hours for Top 10 =
CALCULATE (
[Total Hours],
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Date] >= MIN ( 'Calendar'[Date] )
&& 'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
),
Table[Customer] IN VALUES ( 'Top 10 Customers'[Customer] )
)```

Part 3 does not work yet.  Within the IN VALUES formula you refer to a table, but in step 3 we create a measure, the formula expects a table. So I cannot use this...

Is there another way?