Forum Discussion

v-minkum's avatar
v-minkum
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

Getting incorrect value for the below DAX

I use this formula


_TABLE=
VAR suma = SUM([suma])
VAR savi =
SWITCH(
SELECTEDVALUE(Table1[Names]),
"xcds",1,
SUM(Table3[suma])
)

VAR bp = suma-savi
VAR m= DIVIDE(bp,suma)

RETURN
IF(
ISINSCOPE(Table1[Names]) ,
SWITCH(
SELECTEDVALUE( 'Table2'[Names]),
"1", BLANK(),
"2", savi,
"3", BLANK(),
"4", BLANK()
),
IF(
ISINSCOPE('Table2'[Names]),
SWITCH(
SELECTEDVALUE( 'Table2'[Names]),
"1", suma,
"2", savi,
"3", bp,
"4", FORMAT ( m, "percent" )
)
)
)

But sum of savi is wrong in total as it count excepted value "xcds" which i want set to 1, so if xcds = 100 and others 50 , i got total 150 instead of 51, how to fix that?

1 Reply

  • Angith_Nair's avatar
    Angith_Nair
    Icon for Continued Contributor rankContinued Contributor
    5 years ago

    Hi v-minkum ,

    On the basis of the requirement, you mentioned what I understood is implemented in this measure.

    Could you please try this measure..?

    _TABLE =
VAR suma =
    SUM ( [suma] )
VAR savi =
    SWITCH (
        SELECTEDVALUE ( Table1[Names] ),
        "xcds", 1,
        SUM ( Table3[suma] )
            - CALCULATE ( SUM ( Table3[suma] ), Table1[Names] = "xcds" ) + 1
    )
VAR bp = suma - savi
VAR m =
    DIVIDE ( bp, suma )
RETURN
    IF (
        ISINSCOPE ( Table1[Names] ),
        SWITCH (
            SELECTEDVALUE ( 'Table2'[Names] ),
            "1", BLANK (),
            "2", savi,
            "3", BLANK (),
            "4", BLANK ()
        ),
        IF (
            ISINSCOPE ( 'Table2'[Names] ),
            SWITCH (
                SELECTEDVALUE ( 'Table2'[Names] ),
                "1", suma,
                "2", savi,
                "3", bp,
                "4", FORMAT ( m, "percent" )
            )
        )
    )