Measure total giving wrong result - how to fix?

[gingerclaire]

I have a measure which gives a total of working days (it takes the possible working days in a month and subtracts any holiday or sickness etc.

Working days = ('Dates'[working days])-CALCULATE(SUM(Leave[days off]))

It works fine line by line in a table, but the totals are wrong. I assume it is because it is across two table and therefore the total has no context - but I have no idea how to add it!

How do I make a measure to get the total please? I am a relative beginner so than you in advance for your patience and simple terminology 🙂

[amitchandak]

@gingerclaire , Based on the column in visual

Working days = Sumx(summarize(Leave, Leave[Emp], 'Dates'[Month]),  ('Dates'[working days])-CALCULATE(SUM(Leave[days off])))

Also, refer https://www.youtube.com/watch?v=ufHOOLdi_jk

[gingerclaire]

Thank you - could you tell me what these yellow bits are please and what they relate to (and what they do) as they arent in my original? I would love to understand more.

Working days = Sumx(summarize(Leave, Leave[Emp], 'Dates'[Month]), ('Dates'[working days])-CALCULATE(SUM(Leave[days off])))

[gingerclaire]

I think i have this working BUT:

For those who have null in the 'Leave[days off]' column, their total is also null.

Is there a way to fix this please?

[v-xiaotang]

Hi @gingerclaire 

Thanks for reaching out to us.

Could you please share some sample data and its expected output? Thanks. 

If you are not sure what to share, you can refer to a post I encountered before. The problem description in this post is very detailed.

Best Regards,

Community Support Team _Tang

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

[gingerclaire]

Hi there.

This formula worked well - but it doesn't give any totals for anyone who has not had any days off.

Working days = Sumx(summarize(Leave, Leave[Emp], 'Dates'[Month]), ('Dates'[working days])-CALCULATE(SUM(Leave[days off])))

My 'date' table has a column showing if a day is a 'working day' or holiday'.

My 'leave' table only has rows when leave has been taken - in a column showing a number.

i think because the leave table only shows leave dates - the formula does not work?

I get a result that looks like this (where lines like Baker should total 13, but are blank):