Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

SUMX over dimension table

Hello, im trying to optimize my sumx measure. my goal is to use a table that has fewer rows to make my iteration measure efficient. In my example, i have a trial balance which is my fact table contai...
cardinality
  • Payeras_BI's avatar
    Payeras_BI
    5 years ago

    Hi Anonymous ,

     

    Two things to get the same result in both measures:

    1. A change at the end of your measure:

    TestSumxfromacctytype :=
SUMX (
    'Account Type',
    IF (
        'Account Type'[Account Type] = "PL",
        CALCULATE ( SUM ( TB[amount] ) ),
        CALCULATE (
            SUM ( TB[amount] ) * -.3,
            REMOVEFILTERS ( Glmapping ),
            VALUES ( Glmapping[Company Code] )
        )
    )
)

     2. In your visual slice by the dimensions at the one side of the relationship with your GLmapping table.

    Regards,

Payeras_BI's avatar
Payeras_BI
Icon for Solution Sage rankSolution Sage
5 years ago

Hi Anonymous ,

 

By using Crossjoin you are iterating "a table that contains the Cartesian product of all rows from all tables in the arguments".

 

Accounting type = PL / Company Code A

Accounting type = PL / Company Code B

Accounting type = PL / Company Code C

Accounting type = PL / Company Code D

...

 

Probably not what you want to iterate.

 

Regards,