How to sum the values generated based on Condition

[sivasahit]

Hi ,

  Can any one help me in solving the below issue.

Summary of the issue:

I am having a below Table visualization in Power BI  where I need to create a measure “Expected Result(160) “  in such a way that

 if “Actual Total Hours” is greater than 160 then I should display 160 else I should display the original value .

Logic used:

Actual Total hours= TAW hours + Billable hours+ Maintenance hours

Expected Result(160)= if([Actual Total hours]>160,160, [Actual Total hours])

Expected Result:

Problem :

The above logic is working fine for each row, but when it is coming to Total it is not summarizing the values like other columns , instead displaying 160 like in below image.

Note1: All the above number columns are not readily available in database , I have created in Power BI.

Note2: I have used measure for all the above calculations as I am getting circular dependency error if I am creating calculation in Column.

Current Result:

[VasTg]

@sivasahit 

Use the below measures to get the correct totals.

Actual Total Hours = SUM(Test[TAW Hours])+SUM(Test[Bill Hrs])+SUM(Test[Maint Hrs])

New Expected Result  =

CALCULATE(SUMX(ADDCOLUMNS(SUMMARIZE(Test,Test[Dep],Test[Name]),"New",IF([Actual Total Hours]>160,160,[Actual Total Hours])),[New]))

Replace the Entity and column names.

If this helps, mark the answer as solution.

Kudos are nice too.