cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fcoatis
Post Patron
Post Patron

Hard time figuring out context

Hello all,

Hard time to get the result expected. 

Fig1.png

Every variable has been checked but the problem seem to be the context of "Base" in SUMX:

 

Fig2.png

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
Fcoatis
Post Patron
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

View solution in original post

4 REPLIES 4
Fcoatis
Post Patron
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

View solution in original post

Greg_Deckler
Super User IV
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?


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

@Fcoatis PBIX would definitely help. 


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors