Forum Discussion

omelo's avatar
omelo
Icon for Helper III rankHelper 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

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper 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
Icon for Helper III rankHelper 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