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 :   Market Production center Year L1 Cost Structure L2 Cost Struct...
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.
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.