how to call another column within ISINSCOPE?

[Anonymous]

I have 3 levels on a matrix. (level 3 being the lowest...)

I want to do a measure like:

Cost:=



VAR Sumat = [FixedCosts]+[VariableCosts]



Return



SWITCH (

    TRUE (),

    ISINSCOPE ( Project[proj] ), IF (Sumat <0, 0, [Sumat]), --Level2

    ISINSCOPE ( Project[Comp] ), sum(Level2], -- the aggregation at this level want to be it the sum of level 2

    Sumat  --Level 3

)

That is,  (from lowest to uppest level...

• level 3 is FixedCosts+VariableCosts
• level2 is FixedCosts+VariableCosts (but if it is negative I want to display 0).
• level1 is the sum of level2

[v-yinliw-msft · Accepted solution]

Hi @Anonymous ,

You can try this method:

Here is my test sample data:

Then create the measure of fcost and vcost:

FixedCosts = SUM('Table'[FCosts])

VariableCosts = SUM('Table'[VCosts]) 

Then create 2 measures:

Basic =
VAR _sumat = [FixedCosts] + [VariableCosts]
VAR _sumlevel2 =
    IF ( _sumat < 0, 0, _sumat )
RETURN
    IF (
        ISINSCOPE ( 'Table'[Level3] ),
        _sumat,
        IF ( ISINSCOPE ( 'Table'[Comp] ), _sumlevel2 )
    )

Result =
VAR _LEVEL1 =
    SUMX (
        SUMMARIZE (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Proj] = MAX ( 'Table'[Proj] )
                    && 'Table'[Comp] IN VALUES ( 'Table'[Comp] )
            ),
            'Table'[Proj],
            'Table'[Comp]
        ),
        [Basic]
    )
RETURN
    IF ( ISINSCOPE ( 'Table'[Proj] ), _LEVEL1, [Basic] )

The result is:

Hope this helps you.

Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you @v-yinliw-msft 

Works like a charm!!, thanks... I was trying to add a higher level4 (from Client table) but it doesn't work...

How can I add a higher level from another table?

I try to recreate your steps, but here I do :

ALL ( 'Client' ),

  and then of course the syntax is not valid because I am using data from 'Table' too...

[amitchandak]

@Anonymous , Try for level 1

Suxm(Values( Project[Comp] ), if(Sumat <0 , 0, Sumat ))