Forum Discussion

twister8889's avatar
twister8889
Icon for Helper V rankHelper V
5 years ago
Solved

SUMX and filter help

Hi guys, 

 

I'm trying to sum values but i dont know use the sumx in this case.

 

(
CALCULATE(
SUM(table1[value1]),
FILTER(table1,
FLOOR(max(table1[date1]),0.1)<=[vDate] &&
NOT (table1[type] IN {"A","B","C"})
)
)

*
CALCULATE(
IF(
MAX(table1[value2])=1,1,max(calendar[tax])),
FILTER(table1,
FLOOR(max(table1[date2]),0.1)<=[vDate] &&
NOT (table1[type] IN {"A","B","C"})
)
)
)
the value2 is 0,00 and 1,00 

The expected result value is 3297089,00


Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    twister8889 , You have to use some group by from your visual (Say group column)

     

    a measure like

     

    sumx(Values(table1[Group])
    (
    CALCULATE(
    SUM(table1[value1]),
    FILTER(table1,
    FLOOR(max(table1[date1]),0.1)<=[vDate] &&
    NOT (table1[type] IN {"A","B","C"})
    )
    )

    *
    CALCULATE(
    IF(
    MAX(table1[value2])=1,1,max(calendar[tax])),
    FILTER(table1,
    FLOOR(max(table1[date2]),0.1)<=[vDate] &&
    NOT (table1[type] IN {"A","B","C"})
    )
    )
    ))

     

     

    Also check https://www.youtube.com/watch?v=ufHOOLdi_jk

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    twister8889 , You have to use some group by from your visual (Say group column)

     

    a measure like

     

    sumx(Values(table1[Group])
    (
    CALCULATE(
    SUM(table1[value1]),
    FILTER(table1,
    FLOOR(max(table1[date1]),0.1)<=[vDate] &&
    NOT (table1[type] IN {"A","B","C"})
    )
    )

    *
    CALCULATE(
    IF(
    MAX(table1[value2])=1,1,max(calendar[tax])),
    FILTER(table1,
    FLOOR(max(table1[date2]),0.1)<=[vDate] &&
    NOT (table1[type] IN {"A","B","C"})
    )
    )
    ))

     

     

    Also check https://www.youtube.com/watch?v=ufHOOLdi_jk

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi twister8889 ,

     

    Please use something like :

     

    measure1 =
(
    CALCULATE (
        SUM ( table1[value1] ),
        FILTER (
            table1,
            FLOOR ( table1[date1], 0.1 ) <= [vDate]
                && NOT ( table1[type] IN { "A", "B", "C" } )
        )
    )
        * CALCULATE (
            IF ( MAX ( table1[value2] ) = 1, 1, MAX ( calendar[tax] ) ),
            FILTER (
                table1,
                FLOOR ( table1[date2], 0.1 ) <= [vDate]
                    && NOT ( table1[type] IN { "A", "B", "C" } )
            )
        )
)

     

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

     

    Best Regards,

    Dedmon Dai