excel - How to calculate Share % with a dynamic denominator in a Power BI matrix table? - Stack Overflow

admin管理员组

文章数量:1352069

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

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

• excel
• powerbi
• dax
• powerquery

Share Improve this question Follow edited 2 hours ago desertnaut 60.5k3232 gold badges155155 silver badges181181 bronze badges asked Apr 1 at 5:50 latitude21latitude21 1344 bronze badges New contributor latitude21 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

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

I want to share another solution/measure that works:

Share% = 
VAR denom = CALCULATE(
    SUM(Table1[Value]),
    ALL('Table1'[Financial KPI]),
    'Table1'[Financial KPI] = MAX(Table1[Denominator])
)
VAR num = CALCULATE(
    SUM(Table1[Value]),
    'Table1'[Financial KPI] = MAX(Table1[Financial KPI])
)
RETURN
    DIVIDE(
        num,
        denom,
        0
    )

本文标签： excelHow to calculate Sharewith a dynamic denominator in a Power BI matrix tableStack Overflow