How to calculate Share % with a dynamic denominator in a Power BI matrix table?

[latitude21]

Hey everyone,

I am trying to calculate a (Gross Profit) Share % in a Power BI matrix table. I provided a sample dataset below and the column "Calculated Share Formula" on the right side is what I want to calculate (I posted the whole formula in this column for clarity, but I only need the Share % value itself).

As you can see in the table, depending on the value in the "Financial KPI" column, the needed "Denominator" can change and needs to be "dynamic" variable.

For example, to calculate the Share % for the first row in the example table (with conditions "Store Location = "Tokyo" &  "Year Month" = "2025 January" & "Financial KPI" = "Gross Profit Bicycles"), the DAX Formula should pick the correct (denominator) value for "Total Sales Bicycles" with the same "Store Location" & "Year Month". We need to calculate "Gross Profit Bicycles / Total Sales Bicycles" which is -> "100 / 500 = 0.20" in this case.

Additional conditions:

1) "Store Location" and "Year Month" values can be filtered in slicers to show only desired results.
2) This is only a small sample dataset to calculate (Gross Profit) Share %, but I want to be able to define the correct "Denominator" for each "Financial KPI" in a bigger table.
3) All corresponding Denominator values themselves are already available in the table.

Store Location	Year Month	Financial KPI	Value	Denominator	Desired Share Formula	Calculated Share Formula
Tokyo	2025 January	Gross Profit Bicycles	100	Total Sales Bicycles	Gross Profit Bicycles / Total Sales Bicycles	100 / 500 = 0.20
Tokyo	2025 January	Gross Profit Cars	200	Total Sales Cars	Gross Profit Cars / Total Sales Cars	200 / 800 = 0.25
Tokyo	2025 January	Total Sales Bicycles	500	Total Sales Bicycles	Total Sales Bicycles / Total Sales Bicycles	500 / 500 = 1.00
Tokyo	2025 January	Total Sales Cars	800	Total Sales Cars	Total Sales Cars / Total Sales Cars	800 / 800  = 1.00
New York	2025 January	Gross Profit Bicycles	300	Total Sales Bicycles	Gross Profit Bicycles / Total Sales Bicycles	300 / 1000 = 0.30
New York	2025 January	Gross Profit Cars	500	Total Sales Cars	Gross Profit Cars / Total Sales Cars	500 / 1200 = 0.42
New York	2025 January	Total Sales Bicycles	1000	Total Sales Bicycles	Total Sales Bicycles / Total Sales Bicycles	1000 / 1000 = 1.00
New York	2025 January	Total Sales Cars	1200	Total Sales Cars	Total Sales Cars / Total Sales Cars	1200 / 1200 = 1.00

I have been researching for a solution for some time already, but I still have not found a solution yet. Maybe you have an idea!

I appreciate taking your time and support, thank you in advance!

[rohit1991]

Hi @latitude21 ,

This is a great use case for dynamic DAX calculations in Power BI, and you're absolutely on the right track. What you need is a measure that can calculate the Share % dynamically based on the selected "Financial KPI", using the corresponding "Denominator" value from the same table, while also respecting slicer selections for "Store Location" and "Year Month". Since the denominator is stored as another row within the same table (not as a separate column), the key is to use a DAX measure that looks up the correct denominator value based on matching dimensions.

One effective approach is to create a measure using the LOOKUPVALUE or CALCULATE function with FILTER, where you match the Denominator name, Store Location, and Year Month to retrieve the corresponding denominator value dynamically. You can then divide the current row’s Value by the matched Denominator to compute the desired Share %. This allows full flexibility across different KPIs and ensures the matrix updates correctly with slicer interactions. This kind of dynamic denominator logic is very powerful for financial reporting scenarios like yours and works well even as your dataset grows in size and complexity.

[latitude21]

Hi @rohit1991 ,

thank you for the guidance and solution ideas!
This was the right direction and an example solution can be found in @v-prasare attached PBIX file.

[v-prasare · Accepted solution]

Hi @latitude21,

Thanks for reaching for MS Fabric community support

Please find below attached PBIX file:

Thanks,

Prashanth

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[latitude21]

Hey @v-prasare,

thank you very much, this is the solution I was looking for!

To extend your solution:
If you need the solution to work with multiple selections with slicers or filters (for example selecting multiple store locations or year months), the DAX function can be slightly modified like this:

Share % =
VAR CurrentValue = SUM(FinancialData[Value])

VAR CurrentStore = VALUES(FinancialData[Store Location])

VAR CurrentMonth = VALUES(FinancialData[Year Month])

VAR CurrentKPI = VALUES(FinancialData[Financial KPI])

VAR DenominatorKPI = VALUES(FinancialData[Denominator])

VAR DenominatorValue =

    CALCULATE(

        SUM(FinancialData[Value]),

        FILTER(

            ALL(FinancialData),

            FinancialData[Store Location] = CurrentStore &&

            FinancialData[Year Month] = CurrentMonth &&

            FinancialData[Financial KPI] = DenominatorKPI

        )

    )

RETURN

    DIVIDE(CurrentValue, DenominatorValue)