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.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.