excelpowerbidaxpowerquery

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

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

Most advice I received so far include SWITCH() or SELECTEDVALUE() formulas, but I don't think they are applicable here because I don't select any specific variable. This is one example does not result in the desired outcome.

GrossProfitShare = 
    VAR CurrentFinancialKPI = SELECTEDVALUE('Table'[Financial KPI])
    VAR CurrentStoreLocation = SELECTEDVALUE('Table'[Store Location])
    VAR CurrentYearMonth = SELECTEDVALUE('Table'[Year Month])
    VAR CurrentValue = SELECTEDVALUE('Table'[Value])
    VAR Denominator = 
    SWITCH(
            TRUE(),
            CurrentFinancialKPI = "Gross Profit Bicycles", CALCULATE(MAX('Table'[Value]), 'Table'[Financial KPI] = "Total Sales Bicycles" && 'Table'[Store Location] = CurrentStoreLocation && 'Table'[Year Month] = CurrentYearMonth),
            CurrentFinancialKPI = "Gross Profit Cars", CALCULATE(MAX('Table'[Value]), 'Table'[Financial KPI] = "Total Sales Cars" && 'Table'[Store Location] = CurrentStoreLocation && 'Table'[Year Month] = CurrentYearMonth),
            BLANK()
            )
        RETURN
    IF(NOT ISBLANK(Denominator), DIVIDE(CurrentValue, Denominator), BLANK())

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

Solution

  • it looks like all that you only have one table, then you can try to create a column

    Column =
VAR _sales =
    SEARCH ( "sales", 'Table'[Financial KPI], 1, 0 )
RETURN
    IF (
        _sales > 0,
        1,
        'Table'[Value]
            / MAXX (
                FILTER (
                    'Table',
                    'Table'[Store Location] = EARLIER ( 'Table'[Store Location] )
                        && 'Table'[Year Month] = EARLIER ( 'Table'[Year Month] )
                        && 'Table'[Financial KPI] = EARLIER ( 'Table'[Denominator] )
                ),
                'Table'[Value]
            )
    )

    enter image description here