Forum Discussion

gingerclaire's avatar
gingerclaire
Icon for Helper III rankHelper III
4 years ago

Measure total giving wrong result - how to fix?

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 🙂
DAX
Measure Column Total

5 Replies

    • gingerclaire's avatar
      gingerclaire
      Icon for Helper III rankHelper III
      4 years ago

      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's avatar
      gingerclaire
      Icon for Helper III rankHelper III
      4 years ago

      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's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      gingerclaire
      Icon for Helper III rankHelper III
      4 years ago

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