Forum Discussion

gomezc73's avatar
gomezc73
Icon for Helper V rankHelper V
2 years ago
Solved

Issue to generate a total in a table.

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!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.