How to get total value?

[Yonko]

Hi there. This is my problem - there is a table with project names, tasks and time (for simplicity there is only 1 project). Each project may have different tasks, and each tasks will take different time to complete. There is also an entry "total" which has to be the sum of time of all tasks for every single project. Project and tasks are entries from one table, and time from another - in that another table there is no entry "total", only tasks, and i can't add it, that's why i need to sum it here somehow, which i can't do, that why i'll be glad if you'll help me. I guess that to complete it we need to create another column (or measure), with condition that if task = "total" then we need to sum all time for each project, and if not we just need to take time without changing.

In the end it should be like this:

[v-jianboli-msft · Accepted solution]

Hi @Yonko ,

Based on your description, I have creeated a simple sample:

Please try:

Time = 
VAR _a =
    SUM ( 'Table (2)'[Time] )
VAR _b =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'Table' ), [Project] = MAX ( 'Table'[Project] ) ),
        "Task", [Task]
    )
VAR _c =
    SUMX ( FILTER ( ALL ( 'Table (2)' ), [Task] IN _b ), [Time] )
RETURN
    IF ( MAX ( 'Table'[Task] ) = "Total", _c, _a )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @Yonko 

please try

Time Measure =
VAR CurrentTask =
    SELECTEDVALUE ( Table1[Task] )
VAR NormalValue =
    SUM ( Table2[Time] )
VAR TotalValue =
    CALCULATE ( SUM ( Table2[Time] ), ALL ( Table1[Task] ) )
RETURN
    IF ( CurrentTask = "Total", TotalValue, NormalValue )

[Yonko]

@tamerj1 The problem in your formula happens with TotalValue = it should return sum of values (8), but instead it returns values without changing (3 and 5). If it somehow will return 8 it will work, and i tried different approaches but not succeeded. Any suggestions?
@Anonymous It also returns the same values, not sum of them. I guess it happens because it filtered not only by projects, but also by tasks, so it's granularity moves from project to task and it's not working. I don't know how to fix it sadly 😞

[Anonymous]

Maybe you can share an example file ?

[tamerj1]

@Yonko 
It should work unless the filter context a more complex than described.

[Anonymous]

Your Time column is a measure?

If not create a measure : MeasureNameOfTime = Sum(Table[Time])

Try

Measure Name =

SUMX(ADDCOLUMNS(

SUMMARIZE(

TableName, TableName[Project] , TableName[Task])

,"Invented Column Name",

CALCULATE(

[MeasureNameOfTime])),[Invented Column Name])