Get % of benefit from calculated columns

[Enric21]

Hi there! I need some help.

I would like to get the % of benefit of total amount for projects and global. The point is, columns "Income" and "Expenses" are a result of an "IF" function where: 

Income = IF(CON_DATOS_MENSUALES_ESTUDIOS_ECONOMICOS[DMEE_CATEGORIA] = "40" , CON_DATOS_MENSUALES_ESTUDIOS_ECONOMICOS[DMEE_IMPORTE] , CON_DATOS_MENSUALES_ESTUDIOS_ECONOMICOS[DMEE_IMPORTE]*0)

And expenses (Rest of values) are: 

Expenses = IF(CON_DATOS_MENSUALES_ESTUDIOS_ECONOMICOS[DMEE_CATEGORIA] = "40" , CON_DATOS_MENSUALES_ESTUDIOS_ECONOMICOS[DMEE_IMPORTE]*0 , CON_DATOS_MENSUALES_ESTUDIOS_ECONOMICOS[DMEE_IMPORTE])

From those columns I got the columns "Result" with operating "Income" - "Expenses"

I made the total expenses column: 

CALCULATE( SUMX( ALL(CON_DATOS_MENSUALES_ESTUDIOS_ECONOMICOS) , CON_DATOS_MENSUALES_ESTUDIOS_ECONOMICOS[Expenses]))

Trying to get the benefit % I divided "Result" / "Total expenses" and that made sense just for all the projects but did not for one project

This total expeneses column have the amount of all projects and when I filter by one project does not make sense, is there possible to make an expenses column that gives me the expenses amount for each project?

[amitchandak]

@Enric21 , based on what I got Project ID wise total

Total Expense GT =

CALCULATE( SUMX( filter( ALL(CON_DATOS_MENSUALES_ESTUDIOS_ECONOMICOS) , CON_DATOS_MENSUALES_ESTUDIOS_ECONOMICOS[Project ID] = max( CON_DATOS_MENSUALES_ESTUDIOS_ECONOMICOS[Project ID]) ), CON_DATOS_MENSUALES_ESTUDIOS_ECONOMICOS[Expenses]) )

[Enric21]

Hi Amitchandak! 

Thank you for your quick response I managed to make the "Total Expense GT" as I wanted for. The problem now is that I got the right percentaje when it comes to projects but when it comes to global amount the % is not correct.

I did a new measure replying your response to Result column as: 

Total result GT = CALCULATE( 
    SUMX( 
        filter( 
            ALL(CON_DATOS_MENSUALES_ESTUDIOS_ECONOMICOS) , 
            CON_DATOS_MENSUALES_ESTUDIOS_ECONOMICOS[Project] = max( CON_DATOS_MENSUALES_ESTUDIOS_ECONOMICOS[Project]) 
        ), 
    CON_DATOS_MENSUALES_ESTUDIOS_ECONOMICOS[Result]) 
)

In order to get the benefit % I did 

% benefit = CALCULATE(
    DIVIDE(
	[Total result GT],
	[Total expense GT1]
    )
)

Do you think any way I can get the right benefit % for both, global amount and filtered projects?

Thank you very much.

[v-shex-msft · Accepted solution]

HI @Enric21,

According to your description, it sounds like a common measure total level calculation issue. I'd like to suggest you take a look at Greg's blog to know how to solve this:

Measure Totals, The Final Word 
Regards,

Xiaoxin Sheng

[Enric21]

Hi @v-shex-msft 

Thank you for your response, I found the response. As you said, it was a common measure total level calculation.