Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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)]))))
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |