SUMX and filter help

[twister8889]

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

[v-deddai1-msft]

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

[amitchandak · Accepted solution]

@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