Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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:

Kryssy_2-1649727639886.png

 

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?

1 ACCEPTED SOLUTION

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

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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

Anonymous
Not applicable

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

@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

tamerj1
Super User
Super User

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
Not applicable

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. 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors