topic Re: SUMX not returning right results (from variable tables in a measure) in DAX Commands and Tips

SUMX not returning right results (from variable tables in a measure)

Anonymous — Sun, 04 Jun 2023 21:46:02 GMT

Hi all,
My issue is:
1. Created a measure that identifies var tables
2. I`m trying to return the right results from my table but using SUMX, the measure keeps re-evaluating and gives wrong results

Here is my measure:

"Change=
var tablebase2=

SUMMARIZE(CALCULATETABLE(

'view view_stress_testing_result',KEEPFILTERS('view view_stress_testing_result'[Baseline/Change]="Baseline"),ALL('view view_stress_testing_result'[Source Fiscal Year])),

'view view_stress_testing_result'[Asset Scenario ID],'view view_stress_testing_result'[Source Parameter],'view view_stress_testing_result'[Baseline/Change],'view view_stress_testing_result'[Impacted Date],

"Cashflow",

SUMX(FILTER(('view view_stress_testing_result'),'view view_stress_testing_result'[Impacted Parameter]="Cashflow"&&'view view_stress_testing_result'[Baseline/Change]="Baseline"),'view view_stress_testing_result'[Value]),

"Capex",

SUMX(FILTER(('view view_stress_testing_result'),'view view_stress_testing_result'[Impacted Parameter]="Capex"&&'view view_stress_testing_result'[Baseline/Change]="Baseline"),'view view_stress_testing_result'[Value])

)

var tableIRR3= SUMMARIZE(tablebase2,[Asset Scenario ID],[Source Parameter],"total capex base",SUMX(tablebase2,[Capex]),"IRR base",XIRR(tablebase2,[Cashflow],[Impacted Date]))

var result= SUMX(tableIRR3,[total capex base])

Return result"

I`m comparing by using the same syntax to create tables and I can see the results being returned correctly at each step.
(So creating a table called tablebase2, tableIRR3 and checking the results there) and also through the original data and pivot tables on Excel.

The SUMX seems to be re-evaluating when used in the "var result" based on my visual which contains only "Source Parameter"

Please see data below:

tablebase2 =
"Asset Scenario ID" "Source Parameter" "Baseline/Change" "Impacted Date" "Cashflow" "Capex"

1	FX Rates	Baseline	01/01/2020 00:00:00	-922164.6663
2	FX Rates	Baseline	01/01/2020 00:00:00	-520437.5707
2	FX Rates	Baseline	01/01/2021 00:00:00	314378.5714
1	FX Rates	Baseline	01/01/2021 00:00:00	214134.2835
1	FX Rates	Baseline	01/01/2022 00:00:00	416461.43	303774.1754
2	FX Rates	Baseline	01/01/2022 00:00:00	223200.713	255614.3985
1	FX Rates	Baseline	01/01/2023 00:00:00	876725.5828	159712.9184
2	FX Rates	Baseline	01/01/2023 00:00:00	141828.5901	102293.8374
2	FX Rates	Baseline	01/01/2024 00:00:00	659425.0787	894341.0978
1	FX Rates	Baseline	01/01/2024 00:00:00	112972.2262	989518.2397
1	FX Rates	Baseline	01/01/2025 00:00:00	668644.6012	381667.8337
2	FX Rates	Baseline	01/01/2025 00:00:00	119014.0851	229504.8804
1	FX Rates	Baseline	01/01/2026 00:00:00	725756.1797	364477.9102
2	FX Rates	Baseline	01/01/2026 00:00:00	878225.1378	438402.0113

TableIRR3=
"Asset Scenario ID" "Source Parameter" "total capex base" "IRR base"

2	FX Rates	1920156.2254	0.553920970857143
1	FX Rates	2199151.0774	0.405862980335951

Expected return result = sum of the "total capex base" column = 4,119,307
Actual returned result is 2 times that at = 8,238,615
Leading me to believe what is going on is that the measure is calculating the sum for FX Rates and holding it in 2 rows (hence why the result is duplicated in value).

Also note, my full measure is way bigger and I`m attaching it as a screenshot

Re: SUMX not returning right results (from variable tables in a measure)

Anonymous — Wed, 07 Jun 2023 07:42:54 GMT

Hi Anonymous,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Re: SUMX not returning right results (from variable tables in a measure)

Anonymous — Wed, 07 Jun 2023 08:17:31 GMT

WAIRR Change =

var tablebase2=

SUMMARIZE(CALCULATETABLE(

'view view_stress_testing_result',KEEPFILTERS('view view_stress_testing_result'[Baseline/Change]="Baseline"),ALL('view view_stress_testing_result'[Source Fiscal Year])),

"Cashflow",

"Capex",

)

var tableIRR3= SUMMARIZE(tablebase2,[Asset Scenario ID],[Source Parameter],"total capex base",SUMX(tablebase2,[Capex]),"IRR base",XIRR(tablebase2,[Cashflow],[Impacted Date]))

var tableWIRR3= SUMMARIZE(tableIRR3,[Asset Scenario ID],[Source Parameter],[IRR base],[total capex base],"WIRR base",[IRR base]*[total capex base])

var tableWAIRR3 = SUMMARIZE(tableWIRR3,[Source Parameter],"Sum WIRR",SUMX(tableWIRR3,[WIRR base]),"Sum Capex",SUMX(tableWIRR3,[total capex base]))

var IRRbase= SUMX(tableWAIRR3,[Sum WIRR])

return IRRBASE

Data:

Asset Scenario ID	Asset Scenario	Source Parameter	Impacted Parameter	Source Fiscal Year	Impacted Fiscal Year	Source Date	Impacted Date	Baseline/Change	Stress Direction	Value
1	Asset 1	FX Rates	Cashflow	null	2020	null	01/01/2020	Baseline	null	-922165
1	Asset 1	FX Rates	Cashflow	null	2021	null	01/01/2021	Baseline	null	214134.3
1	Asset 1	FX Rates	Cashflow	null	2022	null	01/01/2022	Baseline	null	416461.4
1	Asset 1	FX Rates	Cashflow	null	2023	null	01/01/2023	Baseline	null	876725.6
1	Asset 1	FX Rates	Cashflow	null	2024	null	01/01/2024	Baseline	null	112972.2
1	Asset 1	FX Rates	Cashflow	null	2025	null	01/01/2025	Baseline	null	668644.6
1	Asset 1	FX Rates	Cashflow	null	2026	null	01/01/2026	Baseline	null	725756.2
2	Asset 2	FX Rates	Cashflow	null	2020	null	01/01/2020	Baseline	null	-520438
2	Asset 2	FX Rates	Cashflow	null	2021	null	01/01/2021	Baseline	null	314378.6
2	Asset 2	FX Rates	Cashflow	null	2022	null	01/01/2022	Baseline	null	223200.7
2	Asset 2	FX Rates	Cashflow	null	2023	null	01/01/2023	Baseline	null	141828.6
2	Asset 2	FX Rates	Cashflow	null	2024	null	01/01/2024	Baseline	null	659425.1
2	Asset 2	FX Rates	Cashflow	null	2025	null	01/01/2025	Baseline	null	119014.1
2	Asset 2	FX Rates	Cashflow	null	2026	null	01/01/2026	Baseline	null	878225.1

Visual: bar chart with y-axis=Source Parameter and x-axis=WAIRR Change.

Expected results:

1956169

Shown results:

3787783

Which seems like XIRR is re-evaluating based on rows of [Source Parameter] only (where I need IRR to be calcualted for each Source parmater and asset ID underneath), and then for some reason multiplied by 2!

I`ll try and upload the PBIX now so that full data would be available too.

Re: SUMX not returning right results (from variable tables in a measure)

Anonymous — Wed, 07 Jun 2023 08:28:48 GMT

I guess in summary, what I`m trying to do is to return the sum of the values already calculated in either:
table tableWIRR3 column "WIRR Base" OR
table tableWAIRR3 column "Sum WIRR"
Without trigerring PowerBI to recalculate the XIRR function in table tableIRR3.

The XIRR is reevaluating as if all rows of the data were for the same asset (perhaps because I`m visualising only Source Parameter? but that is what I need)

I`m unable to upload a PBIX unfortunatly due to company policy.