Forum Discussion

omelo's avatar
omelo
Helper III
4 years ago
Solved

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

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    omelo , Try

     

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

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    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's avatar
      omelo
      Helper III
      4 years ago

      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

  • omelo's avatar
    omelo
    Helper III
    4 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      omelo
      Helper III
      4 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      omelo
      Helper III
      4 years ago

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