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

[TK12345]

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?

[Anonymous]

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] )

2. 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 )

3. 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] )
)

4. 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 )

How to Get Your Question Answered Quickly - Microsoft Fabric Community

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.}

[TK12345]

Hi thanks for your reply, 

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?