Forum Discussion

Mostafa-Hussien's avatar
Mostafa-Hussien
Icon for Helper I rankHelper I
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 basi...
  • MohammadLoran25's avatar
    MohammadLoran25
    3 years ago

    You're welcome,

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

    No worries.

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

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
    3 years ago

    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
      3 years ago

      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 )
      )

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

        SECTORID is not a column on the GENERAL_ACCOUNT table. GENERAL_ACCOUNT table has a DEPTID column that has an active relationship many to one on SECTOR table.

         

        So instead I tried adding GENERAL_ACCOUNT[DEPTID] on the SUMMARIZE, and this time it returned a higher value for the positive value, and unfortunately the total is still 0

         

        I appreciate your response a lot. Any other methods to try ?