Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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,
@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?
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]))))
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,
@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:
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,
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.
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 , need of cross join?
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |