Forum Discussion

jpfeif1's avatar
jpfeif1
New Member
3 years ago
Solved

Question on how to sumif when creating a variable

 

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's avatar
    Anonymous
    3 years ago

    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.

     

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago
    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.
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.