DAX sum amount

[omelo]

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

[Anonymous · Accepted solution]

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

[omelo]

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

[Anonymous]

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.

[omelo]

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]

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]

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 (

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

[omelo]

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

Thank you for your time

[amitchandak]

@omelo , Try

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