Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
vijekris
Regular Visitor

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

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?

 

Untitled.png

 

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

 

 

 

 

0 REPLIES 0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.