Multi level measure with DAX

[T-Ilias]

Hello everyone,

Could you please help me on this calculation ?

I have a table called cost, looks something like this :

Market	Production center	Year	L1 Cost Structure	L2 Cost Structure	L3 Cost Structure	Cost in Mio USD	Sales Volume in Mio
Spain	1B	2022	Consumable material Cat1	Consumable material L2 Cat1	Consumable material L3 Cat1 X	2.3	19
Spain	1B	2022	Packagin material Cat1	Packagin material L2 Cat1	Packagin material L3 Cat1 Y	1.7	19
Spain	1B	2022	Consumable material Cat1	Consumable material L2 Cat1	Consumable material L3 Cat1 Z	3	19
Spain	1B	2022	Packagin material Cat1	Packagin material L2 Cat1	Packagin material L3 Cat1 T	4	19

the measure i'm trying to create is cost per 1k unit and then plug it on matrix to act as below :

on L3 cost structure the measure should display : (Sum(cost in Mio USD)/Sum(sales volume) )* 1000
on L2 cost structure it would sum the lines of L3 cost structure 
on L1 cost structure it would sum the lines of L2 cost structure 

methods i have tried : 

Method 1:

price 1k=

(

    (SUM(Cost[Cost(M USD)]))

/

CALCULATE(

    SUM(Cost[Sales Volume(M)]))

)*1000

RETURN

SUMX(Cost,SVC_Rate)

Method 2 :

L3Level = CALCULATE( SUM( 'Cost'[Cost(M USD)] ) / SUM( 'Cost'[Sales Volume(M)] ) * 1000, ALL( 'Cost' ), 'Cost'[L3 Cost Structure] = SELECTEDVALUE('Cost'[L3 Cost Structure]) )

L2Level = CALCULATE( SUMX( FILTER( ALL( 'Cost' ), 'Cost'[L2 Cost Structure] = SELECTEDVALUE('Cost'[L2 Cost Structure]) ), [L3Level] ), ALL( 'Cost' ) )

L1Level = CALCULATE( SUMX( FILTER( ALL( 'Cost' ), 'Cost'[L1 Cost Structure] = SELECTEDVALUE('Cost'[L1 Cost Structure]) ), [L2Level] ), ALL( 'Cost' ) )

Cost k/unit=

    SWITCH(TRUE(),

        SELECTEDVALUE('Cost'[L3 Cost Structure]), [L3Level],

        SELECTEDVALUE('Cost'[L2 Cost Structure]), [L2Level],

        SELECTEDVALUE('Cost'[L1 Cost Structure]) , [L1Level]

    )

Appreciate your tips and inputs.

Cheers,

[amitchandak]

@T-Ilias , You can use isinscope to handle this, but it work in Bar, Matrix not Table visual

// in method two

SWITCH(TRUE(),
Isinscope('Cost'[L3 Cost Structure]), [L3Level],
Isinscope('Cost'[L2 Cost Structure]), [L2Level],
Isinscope('Cost'[L1 Cost Structure]) , [L1Level]
)

IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM

[T-Ilias · Accepted solution]

Thanks.
I end up doing this : 

   SUMX(

        SUMMARIZE(

            'Cost',

            'Cost'[L1 Cost Structure],

            'Cost'[L2 Cost Structure],

            'Cost'[L3 Cost Structure],

            "CostPerVolume", SUM('Cost'[Cost(M USD)]) / SUM('Cost'[Sales Volume(M)]) * 1000

        ),

        [CostPerVolume]

    )

And it worked. will try the inscope too just to have options in future.