Forum Discussion

siva6063's avatar
siva6063
Regular Visitor
6 years ago

How to do different calculation when drillup

Hi All Thanks in Advance. Im stuck with this requitement, it would be great if i get some help. I have this wiered requirement, that, the drill up to higher level should do a different calculation...
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi siva6063 ,

 

Write a measure llike this.

 

Values =
VAR _inscopecat =
    ISINSCOPE ( 'Table'[Category] )
VAR _inscopesub =
    ISINSCOPE ( 'Table'[Subcategory] )
VAR _totalpricebycat =
    CALCULATE (
        SUM ( 'Table'[Price] ),
        ALLEXCEPT (
            'Table',
            'Table'[Category]
        )
    )
VAR _totaldensitybycat =
    CALCULATE (
        SUM ( 'Table'[Density] ),
        ALLEXCEPT (
            'Table',
            'Table'[Category]
        )
    )
VAR _totalcost =
    SUMX (
        'Table',
        'Table'[Density] * 'Table'[Price]
    )
VAR _totalcostbycat =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Category]
                = MAX ( 'Table'[Category] )
        ),
        'Table'[Density] * 'Table'[Price]
    )
RETURN
    SWITCH (
        TRUE (),
        _inscopesub
            = TRUE (), DIVIDE (
            _totalcostbycat,
            _totaldensitybycat
        ),
        _inscopecat
            = TRUE (), _totalcost
    )

 

 

 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)