Trouble implementing treatas()

[GoingIncognito]

My problem is that I've different tables for a portfolio and a benchmark it is being compared to. These tables are not connected, so I assume I can solve my problem using TREATAS().

I've a measure that works as expected:

Weight%, Portfolio =

CALCULATE( DIVIDE( SUM(Position[MARKET_TOTAL_IN_EUR]), CALCULATE( SUM(Position[MARKET_TOTAL_IN_EUR]), ALLSELECTED(Security[CLASS2]), ALLSELECTED(Security[NAME1]) )) )

But I need to use it on a matrix that has rows from another table: 'Index'[Class1] and 'Index'[Class2]. So I'm thinking I could solve this with a treatas:

CALCULATE( DIVIDE( SUM(Position[MARKET_TOTAL_IN_EUR]), CALCULATE( SUM(Position[MARKET_TOTAL_IN_EUR]), ALLSELECTED(Security[CLASS2]), ALLSELECTED(Security[NAME1]),  TREATAS(VALUES(Index[CLASS2]),Security[CLASS2] ) )) )

But this doesnt work, and neither does treat as with a summarize:

CALCULATE( DIVIDE( SUM(Position[MARKET_TOTAL_IN_EUR]), CALCULATE( SUM(Position[MARKET_TOTAL_IN_EUR]), ALLSELECTED(Security[CLASS2]), ALLSELECTED(Security[NAME1]), TREATAS(Summarize(Index, Index[CLASS1], Index[CLASS2]), Security[CLASS1], Security[CLASS2] ) )) )

What am I doing wrong?

I also can replace the selectedvalues() with IF( isfiltered() ) like this:

if(isfiltered(Security[Name1]), DIVIDE( SUM(Position[MARKET_TOTAL_IN_EUR]), calculate( SUM(Position[MARKET_TOTAL_IN_EUR]), all(Security[Name1])) ),

if(isfiltered(Security[Class2]), DIVIDE( SUM(Position[MARKET_TOTAL_IN_EUR]), calculate( SUM(Position[MARKET_TOTAL_IN_EUR]), all(Security[Class2])) ) ,

if( isfiltered(Security[Class1]), divide( SUM(Position[MARKET_TOTAL_IN_EUR]), calculate( SUM(Position[MARKET_TOTAL_IN_EUR]), all(Security[Class1])) ))))

but again inserting treatas at the end doesn't help. 

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i think you have at least three tables and the problem you have due to no relationship berween another two tables.

So i create three tables to test:

And for your second measure, i make some changes to it:

measure 2 =
CALCULATE (
    DIVIDE (
        SUM ( Position[MARKET_TOTAL_IN_EUR] ),
        CALCULATE (
            SUM ( Position[MARKET_TOTAL_IN_EUR] ),
            ALLSELECTED ( Security[CLASS2] ),
            ALLSELECTED ( Security[NAME1] ),
            TREATAS ( VALUES ( Index[CLASS2] ), Security[CLASS2] ),
            TREATAS ( VALUES ( Security[NAME1] ), Position[name1] )
        )
    )
)

The result shows:

Here is my test pbix file:

pbix 

Best Regards,

Giotto Zhi

[GoingIncognito]

@v-gizhi-msft 

I am dumbfounded how brilliant and intelligent people come to this forum. Going through your file, I feel like "wow, how smart is this person?". Thank you so much!