I was able to get something close to your dsired output:
This is using a matrix visual, three measures for your columns, and an additional measure to capture reusable dynamic formatting string:
Items Recycled_Subtotal% =
VAR _sum = SUM( 'Table'[Items Recycled] )
VAR _sumNewMarketOnly =
CALCULATE(
SUM( 'Table'[Items Recycled] ),
TREATAS( { "New Market" }, 'Table'[Region] )
)
RETURN
IF(
ISFILTERED( 'Table'[Region] ),
_sum,
DIVIDE(
_sumNewMarketOnly,
_sum
)
)
Items Repaired_Subtotal% =
VAR _sum = SUM( 'Table'[Items Repaired] )
VAR _sumNewMarketOnly =
CALCULATE(
SUM( 'Table'[Items Repaired] ),
TREATAS( { "New Market" }, 'Table'[Region] )
)
RETURN
IF(
ISFILTERED( 'Table'[Region] ),
_sum,
DIVIDE(
_sumNewMarketOnly,
_sum
)
)
Items Sold_Subtotal% =
VAR _sum = SUM( 'Table'[Items Sold] )
VAR _sumNewMarketOnly =
CALCULATE(
SUM( 'Table'[Items Sold] ),
TREATAS( { "New Market" }, 'Table'[Region] )
)
RETURN
IF(
ISFILTERED( 'Table'[Region] ),
_sum,
DIVIDE(
_sumNewMarketOnly,
_sum
)
)
Dynamic Format = IF( ISFILTERED( 'Table'[Region] ), "0", "0%" )
For each of the measures (not including [Dynamic Format]), set the format to Dynamic, and put [Dynamic Format] in the Format bar:
Here are the steps to update the matrix visual's formatting to match what I have at the top.
1) Add in Table[Year End] and Table[Region] to Rows and the three measures (Items Recycled_Subtotal%, Items Repaired_Subtotal%, Items Sold_Subtotal%)
2) Rename the measures so they match the columns they are based on
3) Change in Format visual > Visual > Layout and style presets:
- Layout: Tabular
- Repeat row headers: On
4) Change in Format visual > Visual > Blank rows:
- Blank rows: On
- Blank rows > Border: On
5) Change in Format visual > Visual > Row headers:
- +/- icons: Off
6) Change Format visual > Visual > Row subtotals > Per row level: On
6a) For Row level: Region
- Subtotal label: % New Market
6b) For Row level: Year End
- Show subtotal: Off
Note: this removes the "extra" Total row at very bottom
I may not be understanding your question, let me know if the below answer isn't responsive.
To clarify, as noted in my #1 step, you need to create all three measures I define and add them to the matrix visual: [Items Recycled_Subtotal%], [Items Repaired_Subtotal%], and [Items Sold_Subtotal%]. I had sort of lazily put all the measures in one code block, so this point may have been a bit muddled. I just edited my response to clearly deliniate the three calculation measures + measure for dynamic formatting.
The three measures and the dynamic format all toggle on the same thing, namely whether we are in a filter context where 'Table'[Region] is getting filtered (in retrospect, probably ISINSCOPE is the better test in case you have outside filters on Region). There are three levels in the matrix we defined: All (grand total, which we hide) > Year End > Region.
To show more specifically, here is the breakdown of 'is or is not filtered' context in the visual:
If you want to get a better sense of how the measures are working, here are the components split out on Items Sold (they all follow same pattern, so just looking at one should be sufficient).
Sum Regular = SUM( 'Table'[Items Sold] )Sum New Market Only =
CALCULATE(
SUM( 'Table'[Items Sold] ),
TREATAS( { "New Market" }, 'Table'[Region] )
)
As we can see, the regular SUM is both:
- The number to display, at the Region level
- The denominator we want, at the Year End level
And, the New Market sum always just gives us the SUM for Region = 'New Market' for the given Year End. We only need to use it at the Year End level as our numerator.
On your last question re: how is % New Market working, this is just some formatting trickery - the relevant steps are:
- (Step #3) Changing 'Layout and style presets > Layout' to 'Tabular'
Note: besides changing the layout, this automatically moves the row subtotal from the top to the bottom, which you could do without going into Tabular layout via 'Row subtotals > Rows' formatting:
Another note: with subtotal at bottom, the Grand Total goes away. We instead have the repeating Region subtotal at bottom of each Region grouping, and then the single Year End subtotal at bottom. - (Step #6a) As mentioned, the matrix actually adds in new rows with the 'Total' row heading. The word 'Total' is customizable through formatting options, which is what we take advantage of to introduce '% New Market' into the visual. '% New Market' is just the new bottom 'Total' row header relabeled.
Note: in my instructions, we are actually turning on the 'Per row level' toggle to change these settings per level, mainly to turn off subtotals at Year End level and change the subtotal label at Region level. The general concept applies the same, though.
I was able to get something close to your dsired output:
This is using a matrix visual, three measures for your columns, and an additional measure to capture reusable dynamic formatting string:
Items Recycled_Subtotal% =
VAR _sum = SUM( 'Table'[Items Recycled] )
VAR _sumNewMarketOnly =
CALCULATE(
SUM( 'Table'[Items Recycled] ),
TREATAS( { "New Market" }, 'Table'[Region] )
)
RETURN
IF(
ISFILTERED( 'Table'[Region] ),
_sum,
DIVIDE(
_sumNewMarketOnly,
_sum
)
)
Items Repaired_Subtotal% =
VAR _sum = SUM( 'Table'[Items Repaired] )
VAR _sumNewMarketOnly =
CALCULATE(
SUM( 'Table'[Items Repaired] ),
TREATAS( { "New Market" }, 'Table'[Region] )
)
RETURN
IF(
ISFILTERED( 'Table'[Region] ),
_sum,
DIVIDE(
_sumNewMarketOnly,
_sum
)
)
Items Sold_Subtotal% =
VAR _sum = SUM( 'Table'[Items Sold] )
VAR _sumNewMarketOnly =
CALCULATE(
SUM( 'Table'[Items Sold] ),
TREATAS( { "New Market" }, 'Table'[Region] )
)
RETURN
IF(
ISFILTERED( 'Table'[Region] ),
_sum,
DIVIDE(
_sumNewMarketOnly,
_sum
)
)
Dynamic Format = IF( ISFILTERED( 'Table'[Region] ), "0", "0%" )
For each of the measures (not including [Dynamic Format]), set the format to Dynamic, and put [Dynamic Format] in the Format bar:
Here are the steps to update the matrix visual's formatting to match what I have at the top.
1) Add in Table[Year End] and Table[Region] to Rows and the three measures (Items Recycled_Subtotal%, Items Repaired_Subtotal%, Items Sold_Subtotal%)
2) Rename the measures so they match the columns they are based on
3) Change in Format visual > Visual > Layout and style presets:
- Layout: Tabular
- Repeat row headers: On
4) Change in Format visual > Visual > Blank rows:
- Blank rows: On
- Blank rows > Border: On
5) Change in Format visual > Visual > Row headers:
- +/- icons: Off
6) Change Format visual > Visual > Row subtotals > Per row level: On
6a) For Row level: Region
- Subtotal label: % New Market
6b) For Row level: Year End
- Show subtotal: Off
Note: this removes the "extra" Total row at very bottom
I may not be understanding your question, let me know if the below answer isn't responsive.
To clarify, as noted in my #1 step, you need to create all three measures I define and add them to the matrix visual: [Items Recycled_Subtotal%], [Items Repaired_Subtotal%], and [Items Sold_Subtotal%]. I had sort of lazily put all the measures in one code block, so this point may have been a bit muddled. I just edited my response to clearly deliniate the three calculation measures + measure for dynamic formatting.
The three measures and the dynamic format all toggle on the same thing, namely whether we are in a filter context where 'Table'[Region] is getting filtered (in retrospect, probably ISINSCOPE is the better test in case you have outside filters on Region). There are three levels in the matrix we defined: All (grand total, which we hide) > Year End > Region.
To show more specifically, here is the breakdown of 'is or is not filtered' context in the visual:
If you want to get a better sense of how the measures are working, here are the components split out on Items Sold (they all follow same pattern, so just looking at one should be sufficient).
Sum Regular = SUM( 'Table'[Items Sold] )Sum New Market Only =
CALCULATE(
SUM( 'Table'[Items Sold] ),
TREATAS( { "New Market" }, 'Table'[Region] )
)As we can see, the regular SUM is both:
- The number to display, at the Region level
- The denominator we want, at the Year End level
And, the New Market sum always just gives us the SUM for Region = 'New Market' for the given Year End. We only need to use it at the Year End level as our numerator.
On your last question re: how is % New Market working, this is just some formatting trickery - the relevant steps are:
- (Step #3) Changing 'Layout and style presets > Layout' to 'Tabular'
Note: besides changing the layout, this automatically moves the row subtotal from the top to the bottom, which you could do without going into Tabular layout via 'Row subtotals > Rows' formatting:
Another note: with subtotal at bottom, the Grand Total goes away. We instead have the repeating Region subtotal at bottom of each Region grouping, and then the single Year End subtotal at bottom. - (Step #6a) As mentioned, the matrix actually adds in new rows with the 'Total' row heading. The word 'Total' is customizable through formatting options, which is what we take advantage of to introduce '% New Market' into the visual. '% New Market' is just the new bottom 'Total' row header relabeled.
Note: in my instructions, we are actually turning on the 'Per row level' toggle to change these settings per level, mainly to turn off subtotals at Year End level and change the subtotal label at Region level. The general concept applies the same, though.
hello @mobul
please check if this accomodate your need.
1. unpivot your table (since you want to measure % New Market for all Recycled, Repaired, and Sold)
2. since you want to insert measure in row matrix, you need create 4 measures with following DAX
New Market =
CALCULATE(
MAX('Table'[Value]),
'Table'[Region]="New Market"
)
Home =
CALCULATE(
MAX('Table'[Value]),
'Table'[Region]="Home"
)
Established =
CALCULATE(
MAX('Table'[Value]),
'Table'[Region]="Established"
)
% New Market =
var _New =
CALCULATE(
MAX('Table'[Value]),
'Table'[Region]="New Market"
)
var _Total =
SUM('Table'[Value])
Return
DIVIDE(
_New,
_Total
)
4. assign those 4 measures as Matrix Values, Year as Matrix Row, and Attribute as Matrix Column (attribute is consisted of Recyled, Repaired, and Sold).
Hope this will help.
Thank you.
Helpful resources
Power BI Monthly Update - August 2025
Check out the August 2025 Power BI update to learn about new features.
Fabric Community Update - August 2025
Find out what's new and trending in the Fabric community.
