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 based on the results from the previous level . Is this ever possible.

Matrix At Lower Level (SubCategory :

In the table below, category, subcategory, density and price i get from source table

I have a measure in report to calculate cost=Density*Price

Calculation at this hierarchy level is the cost

CategorySubcategoryDensityPricecost
PacketsPlastic

1000

$50$50000
PacketsPlastic11000$50$50000
PacketsPlastic2850$40$34000
CupsPaper550$55$30250
CupsPaper1550$47$25850

 

Matrix when drill up to Category 2

Sshould show up like below. Basically, calculation here is the price, which is

sum (cost for the category) from previous hierarchy level divided by

sum (density for the category) in the previous hierarchy level

 

CategoryDensityPricecost
PacketsSum(1000+1000+850) =2850

Formula =Cost/density

134000/2850=47.017

sum(50000,50000,34000)=134000
CupsSum(550+550)=110056100/1100=51sum(30250+25850)=56100

 

I tried several methods but stil no success. Not sure if i have explained it clearly.

 

Can someone help?

3 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    6 years ago
    What you have explained will result in a circular reference error as Cost at product level needs Price to be calculated, so we can't use that same cost to calculate the price again without it being in a separate field/measure /column.

    You will need to use something like SUMX ultimately to do what you want.
  • kriscoupe's avatar
    kriscoupe
    Solution Supplier
    6 years ago

    Hi siva6063,

     

    You'll need to use a SUMX formula for this one. Something along these lines

     

    SUMX(

    YourTable,

    Density * Price

)

     

    This effectively iterate through all rows of YourTable calculating Density * Price and then adds them all up. This measure should work no matter what level you then drill into.

     

    Hope it helps. If I answered your question please mark my answer as a solution so others can find this in the future.

     

    Kris

  • 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)