Forum Discussion

GoingIncognito's avatar
GoingIncognito
Icon for Advocate III rankAdvocate III
6 years ago
Solved

Trouble implementing treatas()

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. 
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    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

2 Replies

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      GoingIncognito
      Icon for Advocate III rankAdvocate III
      6 years ago

      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!