Search code examples
powerbidaxssas-tabular

Calculate the SUM of the difference and multiplication of some columns

I have the following formula to calculate a KPI :

SUM((PFIINVESTISSEMENT - 
(ENVMTINITIAL-VSTD_ENVMTDISPO))*DPPMTPART)

When I try to apply it in DAX :

KPI1 :=SUM(
DPPMTPART *
([PROP_MtDemandeFin] -(PFIINVESTISSEMENT - 
    (ENVMTINITIAL-VSTD_ENVMTDISPO) )
)

I get the following error :

The SUM function only accepts a column reference as an argument.

Solution

  • You need SUMX iterator function. Using it, your measure should look like this (where Table is the name of your table):

    KPI1 = SUMX('Table', [DPPMTPART] *
   ([PROP_MtDemandeFin] -([PFIINVESTISSEMENT] - ([ENVMTINITIAL]-[VSTD_ENVMTDISPO]))))