cancel
Showing results for
Did you mean:
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:

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

2. Create measure.

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

3. Result:

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

8 REPLIES 8
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)``````

2. Create measure.

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

3. Result:

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

Helper II

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. 👍👍👍👍👍👍

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.

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.

Helper II

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

Helper II

Super User

Hi,

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

Contract Total Measure: =
VAR projecttable =
FILTER (
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 (
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

Helper II

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.

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

Super User

@omelo , Try

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

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!

Announcements