topic Re: SUMX over dimension table in DAX Commands and Tips

SUMX over dimension table

Anonymous — Tue, 22 Dec 2020 05:34:50 GMT

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.

Re: SUMX over dimension table

amitchandak — Tue, 22 Dec 2020 05:51:49 GMT

Anonymous , need of cross join?

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

Re: SUMX over dimension table

Anonymous — Tue, 22 Dec 2020 06:47:57 GMT

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.

Re: SUMX over dimension table

Anonymous — Tue, 22 Dec 2020 07:07:06 GMT

Re: SUMX over dimension table

Payeras_BI — Tue, 22 Dec 2020 08:55:23 GMT

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,

Re: SUMX over dimension table

Payeras_BI — Tue, 22 Dec 2020 10:03:05 GMT

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.

Re: SUMX over dimension table

Anonymous — Tue, 22 Dec 2020 10:35:03 GMT

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?

Re: SUMX over dimension table

Anonymous — Tue, 22 Dec 2020 10:37:41 GMT

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

Re: SUMX over dimension table

Payeras_BI — Tue, 22 Dec 2020 23:06:46 GMT

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,

Re: SUMX over dimension table

Anonymous — Wed, 23 Dec 2020 03:06:53 GMT

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:

What is the difference between ALL and REMOVEFILTERS?
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])?

Re: SUMX over dimension table

Payeras_BI — Wed, 23 Dec 2020 08:37:10 GMT

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,