Help Needed: Finding Minimum Growth Value in Power BI

[Shahabh786]

Hello,

I need assistance with finding the minimum growth value for each region, regardless of the month or year. I'm currently calculating the growth compared to the same period last year using the following measure:

% SPLY Growth = 
VAR _CurrentMonthUnit = [Sales Units]
VAR _SamePeriodLastYearUnits =
    CALCULATE ( [Sales Units], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )
RETURN
    ( _CurrentMonthUnit - _SamePeriodLastYearUnits ) / _CurrentMonthUnit

In my matrix visual, I have 'Geography'[Region Name] on rows and 'Calendar'[Month Year] on columns, with '% SPLY Growth' as values. The 'Month Year' values are formatted like 'Mar-2024, Apr-2024, May-2024'.

I want to create another measure to find the minimum growth value for each region, regardless of the month or year. Here are the attempts I've made:

 
1. Using SUMMARIZECOLUMNS:

Min SPLY Growth = 
MINX(
    SUMMARIZECOLUMNS(
        'Geography'[Region Name],
        'Calendar'[Month Year],
        "@SPLY", [% SPLY Growth]
    ),
    [@SPLY]
)

Error: "SummarizeColumns() and AddMissingItems() may not be used in this context."

2. Using SUMMARIZE:

Min SPLY Growth = 
MINX(
    SUMMARIZE(
        'Sales',
        'Geography'[Region Name],
        'Calendar'[Month Year],
        "@SPLY", [% SPLY Growth]
    ),
    [@SPLY]
)

Error: "Resources Exceeded. This visual has exceeded the available resources. Try filtering to decrease the amount of data displayed."

Could anyone suggest an alternative solution to find the minimum growth value for each region?

Thank you!

[Shahabh786 · Accepted solution]

I was able to finally achive this using below workaround.

ADDCOLUMNS (
                FILTER (
                    SUMMARIZE ( 'Sales', 'Calendar'[MonthYear] ),
                    [MonthYear] <> _CurrentMonthYear
                ),
                "@SPLM", [Monthly Growth %]
            )

[v-yiruan-msft]

Hi @Shahabh786 ,

Has your issue been resolved? If so, could you please mark your post as Answered? Additionally, sharing the solution here would be greatly appreciated, as it can help others in the community who might encounter a similar problem. Thank you for your contribution!

Best Regards

[v-yiruan-msft]

Hi @Shahabh786 ,

Please update the formula of measure [Min SPLY Growth] as below and check if it can return the expected result.

Min SPLY Growth = MINX ( ALL ( 'Calendar'[Month Year] ), [% SPLY Growth] )

If the above one can't help you figure out, please provide some raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards