cancel
Showing results for
Did you mean:  Post Patron

## Hard time figuring out context

Hello all,

Hard time to get the result expected. Every variable has been checked but the problem seem to be the context of "Base" in SUMX: The result expected is the value of Covar in Table1 and what I´m getting is Covar2. I checked the sum of colum .Covar2 in excel and its equal to the expected value Covar. The context is ruled by the date slicer and Asset Filter.

Any help would be fully appreciated.

1 ACCEPTED SOLUTION  Post Patron

Here is what I ended up.

``````.Covar =
VAR Tabela =
FILTER(
SELECTCOLUMNS(
ALLSELECTED(Base[Date]),
"LnAsset",  [.ln],
"LnIBOV", CALCULATE([.ln],Base[Asset]="IBOV")
),
AND (
NOT( ISBLANK( [LnAsset])),
NOT ( ISBLANK( [LnIBOV] ))
)
)
VAR Denom = COUNTROWS(Tabela)-1
VAR Res = SUMX(Tabela, ([LnAsset]-AVERAGEX(Tabela,[LnAsset]))*([LnIBOV]-AVERAGEX(Tabela,[LnIBOV]))/Denom)
RETURN
Res``````
4 REPLIES 4  Post Patron

Here is what I ended up.

``````.Covar =
VAR Tabela =
FILTER(
SELECTCOLUMNS(
ALLSELECTED(Base[Date]),
"LnAsset",  [.ln],
"LnIBOV", CALCULATE([.ln],Base[Asset]="IBOV")
),
AND (
NOT( ISBLANK( [LnAsset])),
NOT ( ISBLANK( [LnIBOV] ))
)
)
VAR Denom = COUNTROWS(Tabela)-1
VAR Res = SUMX(Tabela, ([LnAsset]-AVERAGEX(Tabela,[LnAsset]))*([LnIBOV]-AVERAGEX(Tabela,[LnIBOV]))/Denom)
RETURN
Res``````  Super User IV

@Fcoatis I'm not understanding your use of SUMX here. Result being a variable will always be the same value so I do not believe SUMX(Base,Result) should be any different than just Result. Perhaps you meant to create a table variable and use ADDCOLUMS with the Result calculation as a column in the table and then SUMX across it?

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!  Post Patron

@Greg_Deckler thanks for your answer. I just wanted to add all the values of Result. When I just called Var Result it didnt show the expected value ( -0.0000795701 ). I´m missing the table variable. Just fyi the SUMX(Base, Result) and just Result returned diferent values. I can share the pbix if you like. Thanks again.  Super User IV

@Fcoatis PBIX would definitely help.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User! Announcements #### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories. #### Power BI Dev Camp - September 30th, 2021  