cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
omelo
Helper II
Helper II

DAX sum amount

Hi All,

I need help with a sum, I have no idea what function or expression will give me the expected results.

The data structure is like this:

omelo_1-1638397759477.png

 

I need to show the sum of Amount by create_date, notice that some projects have phases, each one is considered a separate project. So the sum of amount for October should be 225 and November should be 100 (sum of phases). Total Oct-Nov =$325

 

I tried this which excludes the amount of projects with phases, not exactly accurate when it is grouped by month.

Contract Total =

 CALCULATE(

     SUM(Project[Amount]),

FILTER(

   'Project',

   'Project'[Phase]= ""

)

)

 

 

 

I would appreciate any assistance.

 

Thank you

 

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi @omelo ,

Here are the steps you can follow:

1. Create calculated column.

Month = MONTH('Table'[Create_date])
Count = COUNTX(FILTER(ALL('Table'),'Table'[Project]=EARLIER('Table'[Project])),[Project])
Flag =
IF(
    [Count]>1 && ISBLANK('Table'[Phase]),0,1)

vyangliumsft_0-1638951187073.png

2. Create measure.

Measure =
CALCULATE(
    SUM('Table'[Amount]),
    FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])&&'Table'[Flag]=1))

3. Result:

vyangliumsft_1-1638951187074.png

 

Best Regards,

Liu Yang

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

View solution in original post

8 REPLIES 8
v-yangliu-msft
Community Support
Community Support

Hi @omelo ,

Here are the steps you can follow:

1. Create calculated column.

Month = MONTH('Table'[Create_date])
Count = COUNTX(FILTER(ALL('Table'),'Table'[Project]=EARLIER('Table'[Project])),[Project])
Flag =
IF(
    [Count]>1 && ISBLANK('Table'[Phase]),0,1)

vyangliumsft_0-1638951187073.png

2. Create measure.

Measure =
CALCULATE(
    SUM('Table'[Amount]),
    FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])&&'Table'[Flag]=1))

3. Result:

vyangliumsft_1-1638951187074.png

 

Best Regards,

Liu Yang

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

View solution in original post

Hi Liu,

I can't thank you enought  for all your help!

This is giving me the necesary logic to tweak it a little bit and make it work with the data hierarchy on my table.
Thank you again for your response. 👍👍👍👍👍👍

v-yangliu-msft
Community Support
Community Support

Hi  @omelo ,

"If there are phases for the project return the sum amount of the phases, if it doesn't exist, then return Project Amount."

Looking at the chart again, there are phases in October and November, and the "the sum amount of the phases" will be calculated.

So that is, October = 225 November = 101.

vyangliumsft_0-1638779115159.png

Can you explain why the result is "October should be 228 and Nov should be 917"?

 

Best Regards,

Liu Yang

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

Hi Liu,

The amount of the phases roll up to the project level. "If there are phases for the project return the sum amount of the phases, if phases don't exist for the project, then return Project Amount".

October $ 228 (Amount of 136 $225 and 190 $3), Project 123 has phases in November. So it doesn't count in October.

 

November $927 (123 3 phases $100, 150 and 151 (have no phases)$310+$516, Project 185 has one phase $1

 

Thank you for your help

omelo
Helper II
Helper II

Thank you for answering. I added more info on my other reply as I think I didn't explain properly. My apologies for that.
Thanks again for your help.

Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

 

Picture1.png

 

Contract Total Measure: =
VAR projecttable =
FILTER (
ADDCOLUMNS (
SUMMARIZE ( ALL ( data ), Data[Project], Data[Phase] ),
"@amounttotal", CALCULATE ( SUM ( Data[Amount] ) )
),
Data[Phase] <> BLANK ()
)
VAR amounttotal =
SUMX ( projecttable, [@amounttotal] )
VAR nonphaseproject =
EXCEPT (
VALUES ( Data[Project] ),
SUMMARIZE ( FILTER ( ALL ( Data ), Data[Phase] <> BLANK () ), Data[Project] )
)
RETURN
IF (
ISBLANK ( amounttotal ),
CALCULATE ( SUM ( Data[Amount] ), nonphaseproject ),
SUMX (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( data, Data[Project], Data[Phase] ),
"@amounttotal", CALCULATE ( SUM ( Data[Amount] ) )
),
Data[Phase] <> BLANK ()
),
[@amounttotal]
)
)
 
Contract Total Measure TotalFIX: =
SUMX( VALUES( Data[Year-Month CC] ), [Contract Total Measure:] )

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Hi, Thank you for answering! This is not exactly accurate. I'm adding a little bit more data to facilitate explaining.
The amount rolls up to the project level, so I need and expression that:

If there are phases for the project return the sum  amount of the phases, if it doesn't exist , then return Project Amount.

omelo_0-1638462124238.png

In this case, October should be 228 and Nov should be 927

 

Thank you for your time

amitchandak
Super User
Super User

@omelo , Try

 

Contract Total =
CALCULATE(
SUM(Project[Amount]),
FILTER(
allselected('Project'),
eomonth('Project'[Created Date],0)= eomonth(max('Project'[Created Date]),0)
))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors