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

Payeras_BI amitchandak  just to complete the picture:

 

Here is the source data:

 

Here is the diagram:

 

 

I created two sumx measures:

 

from gl mapping table:

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

 

from acct type table:

 

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

 

Here is the result in pivot:

 

so my question is why is their difference in the result if i change the iterating table?

  • 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])?