Issue to generate a total in a table.

[gomezc73]

Hi experts,

 I need help to generate a Grand total in a column. 

 This is my issue:

  I have a column called 'Entered Debit amount', that is a measure with some calculations and it doesn't display a total.

 this is the measure:

R3 Key2 Entered Debit Amount =

   VAR _Key = Selectedvalue(F0902[Key2])

   VAR _Key3 = Selectedvalue(F0902[Key3])

   VAR _Functional = selectedvalue(F0902[FUNCTIONAL_CURRENCY])

   VAR Credit_Entered=

    Calculate(SUMX(Filter(F0902,F0902[LT]="CA" && F0902[Key2]=_Key),F0902[R1 Entered Credit Amount]),

    All(F0902))

VAR Debit_Entered=

    Calculate(SUMX(Filter(F0902,F0902[LT]="CA" && F0902[Key2]=_Key),F0902[R1 Entered Debit Amount]),

    All(F0902))

VAR Credit_Converted=

    Calculate(SUMX(Filter(F0902,F0902[LT]="AA" && F0902[Key2]=_Key),F0902[R1 Converted Credit Amount]),

    All(F0902))

VAR Debit_Converted=

    Calculate(SUMX(Filter(F0902,F0902[LT]="AA" && F0902[Key2]=_Key),F0902[R1 Converted Debit Amount]),

    All(F0902))

VAR deb_Calculada =

IF (SELECTEDVALUE(F0902[CURRENCY_CODE])<>_Functional,

    IF(Debit_Entered<>0 && Credit_Entered=0 && Credit_Converted<>0,0,

       IF(Debit_Entered<>0 && Credit_Entered=0 && Credit_Converted=0,Debit_Entered,

          IF(Debit_Entered=0 && Credit_Entered<>0 && Debit_Converted<>0,Credit_Entered*-1,Debit_Entered))),

              IF(Debit_Entered<>0 && Credit_Entered=0 && Credit_Converted<>0,0,

       IF(Debit_Entered<>0 && Credit_Entered=0 && Credit_Converted=0,Debit_Converted,

          IF(Debit_Entered=0 && Credit_Entered<>0 && Debit_Converted<>0,Credit_Converted*-1,Debit_Converted))))

VAR _SUM_Total = SUMX(SUMMARIZE(

                           F0902,

                           F0902[CURRENCY_CODE],

                           "_VALUE",

                       deb_Calculada),[_VALUE])

RETURN

IF(ISINSCOPE(F0902[CURRENCY_CODE]),deb_Calculada,_SUM_Total)

I am triyng to SUMMARIZE the amounts by currency in the variable '_SUM_Total' but it doesn't work...

any idea will be well received.

Thank you!

[Anonymous · Accepted solution]

Hi @gomezc73 ,

Regarding your problem, there may be a problem with this part of the reference variable.(This should be because the variable is only calculated once.)

This is my test.

Try this.

R3 Key2 Entered Debit Amount =
VAR _Key =
    SELECTEDVALUE ( F0902[Key2] )
VAR _Key3 =
    SELECTEDVALUE ( F0902[Key3] )
VAR _Functional =
    SELECTEDVALUE ( F0902[FUNCTIONAL_CURRENCY] )
VAR Credit_Entered =
    CALCULATE (
        SUMX (
            FILTER ( F0902, F0902[LT] = "CA" && F0902[Key2] = _Key ),
            F0902[R1 Entered Credit Amount]
        ),
        ALL ( F0902 )
    )
VAR Debit_Entered =
    CALCULATE (
        SUMX (
            FILTER ( F0902, F0902[LT] = "CA" && F0902[Key2] = _Key ),
            F0902[R1 Entered Debit Amount]
        ),
        ALL ( F0902 )
    )
VAR Credit_Converted =
    CALCULATE (
        SUMX (
            FILTER ( F0902, F0902[LT] = "AA" && F0902[Key2] = _Key ),
            F0902[R1 Converted Credit Amount]
        ),
        ALL ( F0902 )
    )
VAR Debit_Converted =
    CALCULATE (
        SUMX (
            FILTER ( F0902, F0902[LT] = "AA" && F0902[Key2] = _Key ),
            F0902[R1 Converted Debit Amount]
        ),
        ALL ( F0902 )
    )
VAR deb_Calculada =
    IF (
        SELECTEDVALUE ( F0902[CURRENCY_CODE] ) <> _Functional,
        IF (
            Debit_Entered <> 0
                && Credit_Entered = 0
                && Credit_Converted <> 0,
            0,
            IF (
                Debit_Entered <> 0
                    && Credit_Entered = 0
                    && Credit_Converted = 0,
                Debit_Entered,
                IF (
                    Debit_Entered = 0
                        && Credit_Entered <> 0
                        && Debit_Converted <> 0,
                    Credit_Entered * -1,
                    Debit_Entered
                )
            )
        ),
        IF (
            Debit_Entered <> 0
                && Credit_Entered = 0
                && Credit_Converted <> 0,
            0,
            IF (
                Debit_Entered <> 0
                    && Credit_Entered = 0
                    && Credit_Converted = 0,
                Debit_Converted,
                IF (
                    Debit_Entered = 0
                        && Credit_Entered <> 0
                        && Debit_Converted <> 0,
                    Credit_Converted * -1,
                    Debit_Converted
                )
            )
        )
    )
VAR _SUM_Total =
    SUMX (
        SUMMARIZE (
            F0902,
            F0902[CURRENCY_CODE],
            "_VALUE",
                IF (
                    SELECTEDVALUE ( F0902[CURRENCY_CODE] ) <> _Functional,
                    IF (
                        Debit_Entered <> 0
                            && Credit_Entered = 0
                            && Credit_Converted <> 0,
                        0,
                        IF (
                            Debit_Entered <> 0
                                && Credit_Entered = 0
                                && Credit_Converted = 0,
                            Debit_Entered,
                            IF (
                                Debit_Entered = 0
                                    && Credit_Entered <> 0
                                    && Debit_Converted <> 0,
                                Credit_Entered * -1,
                                Debit_Entered
                            )
                        )
                    ),
                    IF (
                        Debit_Entered <> 0
                            && Credit_Entered = 0
                            && Credit_Converted <> 0,
                        0,
                        IF (
                            Debit_Entered <> 0
                                && Credit_Entered = 0
                                && Credit_Converted = 0,
                            Debit_Converted,
                            IF (
                                Debit_Entered = 0
                                    && Credit_Entered <> 0
                                    && Debit_Converted <> 0,
                                Credit_Converted * -1,
                                Debit_Converted
                            )
                        )
                    )
                )
        ),
        [_VALUE]
    )
RETURN
    IF ( ISINSCOPE ( F0902[CURRENCY_CODE] ), deb_Calculada, _SUM_Total )

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