Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dax Formula Not adding up with filter

Hello Power BI Community,

I have an issue with a DAX formula:

Basically, I want to have a target # of hours per month that is fixed by 105,  so I divided 105 by # of Days in a month.

Then, I multiply the # of days of each month a person works to find their target each month.

 

However, when I select on my filter all months this is not working here is what happens:

 

Formula = # of Days Worked * # of Hours Per Day

 

So, in the column Target Hours its totaling 210 instead of 207 because its multiplying 3.5 by 60. Is there a way around this so that it wont multiply by the 3.5 and add up instead to 207? 

 

If anyone can help me please?

  • tamerj1's avatar
    tamerj1
    4 years ago

    Anonymous 
    No. I am referencing [Target] as measure. Please try and let me know the results

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    SORRY, I didnt understand before and thank you so much for being patient with me. It works and THANK YOU SO MUCH!!!!!!

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    HI Anonymous 
    The names of columns and tables and the relationships are not clear to me. However you may try 

     

    Target New =
SUMX ( SUMMARIZE ( 'Table', 'Table'[Provider ID], 'Date'[Month] ), [Target] )

    or

    Target New =
SUMX (
    CROSSJOIN ( VALUES ( 'Table'[Provider ID] ), VALUES ( 'Date'[Month] ) ),
    [Target]
)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Tamerj1,

      In the formula you provided it references [Target} as a column, however I am unable to do that because its not a column its a DAX formula. I did try to use it but it didnt work. Sorry, maybe I am not understanding it properly? Is it referring to a column [Target} because if I try to put a measure in it, it does not accept it only a column. 

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Anonymous 
        No. I am referencing [Target] as measure. Please try and let me know the results

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Tamerj1, those are all dax formulas not columns, so I cant use those formulas. Atleast, I dont think I can, cause I tried Target is not a column its a formula

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      Anonymous 
      I know that. I was talking about the Provider ID and the Month columns. You may try the formula I've propose (Most prpobaly the 2nd one) and let me know how it goes. Thank you