Forum Discussion

Mostafa-Hussien's avatar
3 years ago
Solved

Cant get correct total on complex model

I have a complex data model imported from Oracle database, I am attaching a drawing of how it looks like.

 

 

I made a measure on a fact table "General Account" that works fine. It is basically like this

Measure1 = 
SUMX(
    FILTER(GENERAL_ACCOUNT,
    LEFT(GENERAL_ACCOUNT[ACCOUNTCODE],5) = "21031"),
    (GENERAL_ACCOUNT[DEBITVALUE] - GENERAL_ACCOUNT[CREDITVALUE])
)

The problem comes when I want to aggregate only positive values of this measure and display it in a table/matrix using dimension tables columns that have some active and some inactive relationships

The SUMX I use in my attempt to filter only positive values works on line values but not on total

Here is the first I tried without filtering positive values, which returns correct line and total values

Measure2 = 
CALCULATE(
    SUMX(
        GENERAL_ACCOUNT,
        [Measure1]
    ),
    ALLSELECTED(GENERAL_ACCOUNT[DEPTID], GENERAL_ACCOUNT[CLIENTID], GENERAL_ACCOUNT[DOCDATE], GENERAL_ACCOUNT[DOCNO]),
    --acc
    USERELATIONSHIP(DimLink[LinkCode],ACCOUNTS[Link]),
    USERELATIONSHIP(DimBU[BUCode], ACCOUNTS[BU]),
    --dep
    USERELATIONSHIP(DimLink[LinkCode], DEPARTMENTS[Link]),
    USERELATIONSHIP(DimBU[BUCode], DEPARTMENTS[BU]),
    --sector
    USERELATIONSHIP(DimLink[LinkCode], SECTORS[Link]),
    USERELATIONSHIP(DimBU[BUCode], SECTORS[BU]),
    --general acc
    USERELATIONSHIP(DimLink[LinkCode], GENERAL_ACCOUNT[Link]),
    USERELATIONSHIP(DimBU[BUCode], GENERAL_ACCOUNT[BU])
)

And here is my attempt for filtering on positive values which failed on total line

Measure3 = 
var int_talbe = 
    ADDCOLUMNS(
    SUMMARIZE(GENERAL_ACCOUNT,
    GENERAL_ACCOUNT[BU],
    GENERAL_ACCOUNT[Link],
    GENERAL_ACCOUNT[ACCOUNTCODE]),
    "value",
CALCULATE(
    SUMX(
        GENERAL_ACCOUNT,
        [Measure1]
    ),
    ALLSELECTED(GENERAL_ACCOUNT[DEPTID], GENERAL_ACCOUNT[CLIENTID], GENERAL_ACCOUNT[DOCDATE], GENERAL_ACCOUNT[DOCNO]),
    --acc
    USERELATIONSHIP(DimLink[LinkCode],ACCOUNTS[Link]),
    USERELATIONSHIP(DimBU[BUCode], ACCOUNTS[BU]),
    --dep
    USERELATIONSHIP(DimLink[LinkCode], DEPARTMENTS[Link]),
    USERELATIONSHIP(DimBU[BUCode], DEPARTMENTS[BU]),
    --sector
    USERELATIONSHIP(DimLink[LinkCode], SECTORS[Link]),
    USERELATIONSHIP(DimBU[BUCode], SECTORS[BU]),
    --general acc
    USERELATIONSHIP(DimLink[LinkCode], GENERAL_ACCOUNT[Link]),
    USERELATIONSHIP(DimBU[BUCode], GENERAL_ACCOUNT[BU])
)
    )
return
SUMX(
    int_table,
    IF([value]<0,0,[value])
)

Here is how both measures behave 

 

Any help?

  • MohammadLoran25's avatar
    MohammadLoran25
    3 years ago

    You're welcome,

    Yes I tried it in the file you sent and it worked.

    No worries.

24 Replies

  • Hi Mostafa-Hussien ,

    I think it is because of the ALLSELECTED function that you have in Measure3.

    Please comment it and let me know the result.

     

    Regards,

    Loran

    • Mostafa-Hussien's avatar
      Mostafa-Hussien
      Icon for Helper I rankHelper I

      I cannot drop the ALLSELETED(), otherwise it will not aggregate regardless of the ALLSELECTED arguments.

      I mean; I added certain columns to ALLSELECTED like CLIENTID, because I want an aggregate value regardless of CLIENTID

      • MohammadLoran25's avatar
        MohammadLoran25
        Icon for Solution Sage rankSolution Sage

        Not the best solution, but as an easy and fast solution to fix it, please try this as well. It should work for your total:

        Measure =
        VAR _TABLEE =
            ADDCOLUMNS (
                SUMMARIZE (
                    GENERAL_ACCOUNT,
                    GENERAL_ACCOUNT[BU],
                    GENERAL_ACCOUNT[Link],
                    GENERAL_ACCOUNT[ACCOUNTCODE]
                ),
                "@measure3", [measure3]
            )
        RETURN
            SUMX ( _TABLEE, [@measure3] )

         

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion

    Hi Mostafa-Hussien 

    please try

    Measure3 =
    VAR SelectedDims =
    ALLSELECTED (
    GENERAL_ACCOUNT[DEPTID],
    GENERAL_ACCOUNT[CLIENTID],
    GENERAL_ACCOUNT[DOCDATE],
    GENERAL_ACCOUNT[DOCNO]
    )
    RETURN
    SUMX (
    SUMMARIZE (
    GENERAL_ACCOUNT,
    GENERAL_ACCOUNT[BU],
    GENERAL_ACCOUNT[Link],
    GENERAL_ACCOUNT[ACCOUNTCODE]
    ),
    VAR Value1 =
    CALCULATE (
    SUMX ( GENERAL_ACCOUNT, [Measure1] ),
    SelectedDims,
    --acc
    USERELATIONSHIP ( DimLink[LinkCode], ACCOUNTS[Link] ),
    USERELATIONSHIP ( DimBU[BUCode], ACCOUNTS[BU] ),
    --dep
    USERELATIONSHIP ( DimLink[LinkCode], DEPARTMENTS[Link] ),
    USERELATIONSHIP ( DimBU[BUCode], DEPARTMENTS[BU] ),
    --sector
    USERELATIONSHIP ( DimLink[LinkCode], SECTORS[Link] ),
    USERELATIONSHIP ( DimBU[BUCode], SECTORS[BU] ),
    --general acc
    USERELATIONSHIP ( DimLink[LinkCode], GENERAL_ACCOUNT[Link] ),
    USERELATIONSHIP ( DimBU[BUCode], GENERAL_ACCOUNT[BU] )
    )
    RETURN
    IF ( Value < 0, 0, Value1 )
    )

    • Mostafa-Hussien's avatar
      Mostafa-Hussien
      Icon for Helper I rankHelper I

      Hi tamerj1 

      Thanks for your reponse

      Unfortunately it gives the same correct line value and 0 on total

      Any thing else in mind ?

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion

        Mostafa-Hussien 

        Try

        Measure3 =
        VAR SelectedDims =
        ALLSELECTED (
        GENERAL_ACCOUNT[DEPTID],
        GENERAL_ACCOUNT[CLIENTID],
        GENERAL_ACCOUNT[DOCDATE],
        GENERAL_ACCOUNT[DOCNO]
        )
        RETURN
        SUMX (
        SUMMARIZE (
        GENERAL_ACCOUNT,
        GENERAL_ACCOUNT[BU],
        GENERAL_ACCOUNT[Link],
        GENERAL_ACCOUNT[ACCOUNTCODE],

        GENERAL_ACCOUNT[SECTORID]
        ),
        VAR Value1 =
        CALCULATE (
        SUMX ( GENERAL_ACCOUNT, [Measure1] ),
        SelectedDims,
        --acc
        USERELATIONSHIP ( DimLink[LinkCode], ACCOUNTS[Link] ),
        USERELATIONSHIP ( DimBU[BUCode], ACCOUNTS[BU] ),
        --dep
        USERELATIONSHIP ( DimLink[LinkCode], DEPARTMENTS[Link] ),
        USERELATIONSHIP ( DimBU[BUCode], DEPARTMENTS[BU] ),
        --sector
        USERELATIONSHIP ( DimLink[LinkCode], SECTORS[Link] ),
        USERELATIONSHIP ( DimBU[BUCode], SECTORS[BU] ),
        --general acc
        USERELATIONSHIP ( DimLink[LinkCode], GENERAL_ACCOUNT[Link] ),
        USERELATIONSHIP ( DimBU[BUCode], GENERAL_ACCOUNT[BU] )
        )
        RETURN
        IF ( Value < 0, 0, Value1 )
        )