Using ISINSCOPE to calculate at a specific hierarchy level and show the total

[vijekris]

Hello All,

I'm calculating a percentage of total at various hierarchy levels and using this to get the value in column "RegionShare $"

I want to change the RegionShare$ column to show only the values at the subregion (PINK) level (rest of the hierarchy values should be null) and add them up

I have the following hierarchy:

1. Subregion - Level 1 (Pink)

2. Segment - Level 2 (yellow)

3. Product - Level 3 (Green)

So the expected RegionShare$ sum on the Total row at the bottom  = -$2.1M (which is adding up all the values in PINK hierarchy. How do I achieve this?

The forumla for RegionShare$ is:

RegionShare $ = 

VAR WW_Rate = CALCULATE([Current Rate],ALLSELECTED('Table'[subregion (groups)]))
VAR WW_Current_TPV = CALCULATE([Current Period AMT],ALLSELECTED('Table'[subregion (groups)]))
VAR region_share_gain = IF(ISINSCOPE('Table'[subregion (groups)]),[Current Share])-IF(ISINSCOPE('Table'[subregion (groups)]),[Prior Share %])


return  region_share_gain*WW_Current_TPV*([Current Period Rate]-WW_Rate)

In order to calculate the PriorShare% and the Current Share%, I used ISINSCOPE:

Current Share = 

SWITCH(
    TRUE(),
    ISINSCOPE ('Table'[Product (groups)]),
    DIVIDE (
        [Current Period AMT],
        CALCULATE (
           [Current Period AMT],
            ALLSELECTED ( 'Table'[Product (groups)], ‘Table’[sales_seg_name])
        )
    ),
    ISINSCOPE(‘Table’[sales_seg_name]),
    DIVIDE (
        [Current Period AMT],
        CALCULATE (
           [Current Period AMT],
            ALLSELECTED (‘Table’[sales_seg_name]))),

           ISINSCOPE(‘Table’[subregion (groups)]),
    DIVIDE (
        [Current Period AMT],
        CALCULATE (
           [Current Period AMT],
            ALLSELECTED (‘Table’[subregion (groups)]))))