Question on how to sumif when creating a variable

[jpfeif1]

I have the following Dax formula. 

Y2023 = rounddown(CALCULATE(sum(EROSION[Value]), EROSION[DATA TYPE] in {"FUNNEL","Baseline","Forecast"},EROSION[Year]="Y2023")/1000000,1)

I would the ability to change this so only the "Baseline" erosion type will be /2000000,1  and Funnel and Forecast be remain /1000000,1

Any ideas would be appreciated.

[Anonymous · Accepted solution]

Hi @jpfeif1 

You can try the following code:

Y2023 = switch(true(),EROSION[DATA TYPE] in {"FUNNEL","Forecast"},rounddown(CALCULATE(sum(EROSION[Value]),EROSION[Year]="Y2023")/1000000,1),EROSION[DATA TYPE] in {"Baseline"},rounddown(CALCULATE(sum(EROSION[Value]),EROSION[Year]="Y2023")/2000000,1))

Best Regards!

Yolo Zhu

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

[FreemanZ]

hi @jpfeif1 

try like:

Y2023 =

rounddown(

    CALCULATE(

         sum(EROSION[Value]),

         EROSION[DATA TYPE] in {"FUNNEL","Forecast"},

         EROSION[Year]="Y2023"

     )/1000000,

    1

)

+

rounddown(

         CALCULATE(

                  sum(EROSION[Value]), 

                  EROSION[DATA TYPE] ="Baseline",

                  EROSION[Year]="Y2023"

          )/2000000,

        1

)

or

Y2023 =

ROUNDDOWN(

SUMX(

    FILTER(

       EROSION,

       EROSION[Year]="Y2023"

         &&EROSION[DATA TYPE] in {"FUNNEL","Baseline","Forecast"},

    ),

    IF(

       EROSION[DATA TYPE] ="Baseline",

       EROSION[Value]/2000000,

       EROSION[Value]/1000000

    )

),1)

in case of issue, please share some sample data.