Forum Discussion

ChirSidh's avatar
ChirSidh
Icon for Helper II rankHelper II
3 years ago

Sum up to Project level from Sub project level - DAX

In my excel sheet i have Month, Project Num and Sub-Project Num. each project have multiple sub projects, i have revenue / cost = multplier for all the subprojects. I want to create a conditional column to calculate the same at project.
In the attachment i have shown the expected result, can somebody help me how to create DAX Formula 

 

Need Help

3 Replies

  • ChirSidh's avatar
    ChirSidh
    Icon for Helper II rankHelper II
    3 years ago

    i am not able to find option for attaching the excel file

     

  • yakm's avatar
    yakm
    Frequent Visitor
    3 years ago

    divide(revenue, cost), use measure not column

  • ChirSidh's avatar
    ChirSidh
    Icon for Helper II rankHelper II
    3 years ago
    I have used this formula but its not giving exactly right answer, 
    Avg Multiplier = DIVIDE(
        CALCULATE(
            SUM('Multiplier Movement'[Current Forecast Gross Revenue]),
            'Multiplier Movement'[ Project] = EARLIER('Multiplier Movement'[ Project]),
            'Multiplier Movement'[Month] = EARLIER('Multiplier Movement'[Month])
        ),
        CALCULATE(
            SUM('Multiplier Movement'[Current Forecast Direct Labour (SCR)]),
            'Multiplier Movement'[ Project] = EARLIER('Multiplier Movement'[ Project]),
            'Multiplier Movement'[Month] = EARLIER('Multiplier Movement'[Month]
        )
    )
    )