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,

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , need of cross join?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    i do have a sample file how do i attach in this message? i use cross join, since the i use two different dimension table as my iteration, acct type and company code.