SUMX over dimension table

[newbiepowerbi]

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 containing the gl code, company code and amount. above it is gl mapping table which contains the gl code, gl name, company code and acct type. finally  above the gl mapping table, i created two dimension tables , company code and acct type.

to illustrate:

company code table : accty type table

gl mapping

trial balance

I have created a simple sumx measure like this:

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

the above measure works fine, but i wanted to change the table to refer to the a cross join of the two above dimension table:

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

i having a weird result. appreciate if you can explain me why. I do have sample power pivot file, dont know how to attach it in this message to better illustrate my question. 

[newbiepowerbi]

@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?

[newbiepowerbi]

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 · Accepted solution]

Hi @newbiepowerbi ,

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,

[newbiepowerbi]

@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]

Hi @newbiepowerbi ,

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,

[Payeras_BI]

Re:  "I do have a sample file how do i attach in this message?"

You can use a onedrive, google drive or similar link to upload your files.

[Payeras_BI]

Hi @newbiepowerbi ,

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,

[newbiepowerbi]

[amitchandak]

@newbiepowerbi , need of cross join?

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

[newbiepowerbi]

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.