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 MeasureNovember21224November 12424November1840720Total 21 768

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

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_4Em1GmstsBVsGE3kBAdl6VMe_oNssnAV6zykNOA?e=SRwfCK

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

Measure not calculating on each row | Microsoft Fabric Community

13 Replies

Greg_Deckler
Community Champion
6 years ago
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
  Not applicable
  6 years ago
  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_4Em1GmstsBVsGE3kBAdl6VMe_oNssnAV6zykNOA?e=SRwfCK
  - amitchandak
    Super User
    6 years ago
    It seems like a calendar

amitchandak

Super User

6 years ago

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

Anonymous
Not applicable
6 years ago
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_4Em1GmstsBVsGE3kBAdl6VMe_oNssnAV6zykNOA?e=ogkXCc
- v-kelly-msft
  Community Support
  6 years ago
  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
    Not applicable
    6 years ago
    Hi v-kelly-msft
    
    It should be back online now.
    
    Csv file
    
    Best Regards,
    Queenie
v-kelly-msft
Community Support
6 years ago
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
  Not applicable
  6 years ago
  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

Forum Discussion

Measure not calculating on each row

13 Replies

Related content

DAX Calculated Measure

DAX Measure for Ranking the valid rows and calculating the average rating

Measure or Calculated Column

Measure to calculate

Measure/Model calculation support needed

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