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,

Anonymous's avatar
Anonymous
Not applicable
5 years ago

correct here is my sumx measure using acct type table:

TestSumxfromacctytype:=SUMX(
	'Account Type',
	IF(
		'Account Type'[Account Type]="PL",
		CALCULATE(SUM(TB[amount])),
		CALCULATE(SUM(TB[amount])*-.3, ALLEXCEPT(Glmapping,Glmapping[Company Code]))))
Payeras_BI's avatar
Payeras_BI
Icon for Solution Sage rankSolution Sage
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,

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Payeras_BI thank you for the feedback, it worked using powerbi :). However, since im using power pivot, i think removefilters is not available. what i did is use all instead of removefilters. 

     

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

     

     

    It worked also. Given this, my questions are:

    1. What is the difference between ALL and REMOVEFILTERS?
    2. If both cases, we remove the filter, then add another filter for the company code,so what is the difference between this approach and using ALLexcept(GLMapping, GLMapping[Company Code])?
    • Payeras_BI's avatar
      Payeras_BI
      Icon for Solution Sage rankSolution Sage
      5 years ago

      Hi Anonymous ,

       

      1. Yes, REMOVEFILTERS is just an alias for ALL when used as a CALCULATE modifer.

       

      2. Here you need to remove filters from GLMapping and restoring it for againg for GLMapping[Company Code] taking into account the previous cross-filtering.

       

      https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

       

      Regards,