Forum Discussion

T-Ilias's avatar
T-Ilias
Regular Visitor
2 years ago
Solved

Multi level measure with DAX

Hello everyone,

 

Could you please help me on this calculation ?

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

 

MarketProduction centerYearL1 Cost StructureL2 Cost StructureL3 Cost StructureCost in Mio USDSales Volume in Mio
Spain1B2022Consumable material Cat1Consumable material L2 Cat1Consumable material L3 Cat1 X2.319
Spain1B2022Packagin material Cat1Packagin material L2 Cat1Packagin material L3 Cat1 Y1.719
Spain1B2022Consumable material Cat1Consumable material L2 Cat1Consumable material L3 Cat1 Z319
Spain1B2022Packagin material Cat1Packagin material L2 Cat1Packagin material L3 Cat1 T419

 

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,

 

Need Help
  • T-Ilias's avatar
    T-Ilias
    2 years ago

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      T-Ilias
      Regular Visitor
      2 years ago

      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.