Measure not calculating on each row

[Anonymous]

Hi guys,

I am trying to calculate the total hours scheduled for each month. To do this I have 1 measure that calculates the amount of active employees, a column with the average hours.

Current Employees = CALCULATE(COUNTx(FILTER(Schedules;Schedules[StartDate]<=max('Calendar'[Date]) && (ISBLANK(Schedules[EndDate]) || Schedules[EndDate]>max('Calendar'[Date])));(Schedules[Employment]));CROSSFILTER(Schedules[StartDate];'Calendar'[Date];None))

The average hours can be 12/24/36/40

If I try to calculate the total hours it will summarize the average hours and multiply it by the current employees which gives a too large amount.

If I don't summarize the total hours it will only calculate a few random rows.

The measure I use is:

Measure = CALCULATE([Current Employees] * SUMX(Schedules; Schedules[AverageHours]))

I'll add two screenshots of what happens, but what I'd like to see is something like this:

Month	Current Employees	Average Hours	Measure
November	2	12	24
November	1	24	24
November	18	40	720
Total	21		768

[v-kelly-msft]

Hi @Anonymous ，

Correct your measure as below:

Measure = CALCULATE(MAX('Schedules'[Current Employees]) * SUMX('Schedules','Schedules'[AverageHours]))

And you will see:

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Thank you so much for helping me @v-kelly-msft!

But I can't seem to use the measure [Current Employees] in a MAX function.

Any other ideas?

Best regards,

Queenie Barends

[Anonymous]

@v-kelly-msft @Greg_Deckler @amitchandak 

Sorry it seems like I linked to the wrong data.

The previous link should now work.

https://iblsoftware1-my.sharepoint.com/:x:/g/personal/queenie_barends_truelime_nl/EZVu11_4Em1GmstsBV...

[v-kelly-msft]

Hi @Anonymous ,

Sorry for the late reply,but the sample data has been removed,can you update it?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @v-kelly-msft 

It should be back online now.

Csv file 

Best Regards,

Queenie

[v-kelly-msft]

Hi @Anonymous ，

Can you advise me what is in [AverageHours]?Is it a measure?I cant reappear the table you showed in the oringinal post,can you share me your .pbix file if it's not confidential?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @v-kelly-msft ,

I cant share the original pbix since it has employee information in it.

Average hours is the hours an employee should work in a week (like 40 hours or 32).

I updated the csv to show the average hours since hours does nothing more than show 40.

Best Regards,

Queenie

[amitchandak]

This multiplication need to be done at some context level like Month or employee ID, Based on that use formula like this

Measure = CALCULATE([Current Employees] * SUMX(Schedules; Schedules[AverageHours]), values(date[Month]))
Measure = Sumx(SUMMARIZE(Table, date[Month],"_sum"[Current Employees] * SUMX(Schedules; Schedules[AverageHours])),[_sum])

[Greg_Deckler]

Can you post the source data as text? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Hi @Greg_Deckler ,

I made an excel file with the data you should need.

Let me know if you need anything else.

https://iblsoftware1-my.sharepoint.com/:x:/g/personal/queenie_barends_truelime_nl/EZVu11_4Em1GmstsBV...

[v-kelly-msft]

Hi @Anonymous ，

It' s an excel about calendar table,no extra data,can you update it?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

I'm with @amitchandak , that's just a calendar.

[amitchandak]

It seems like a calendar