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

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