Forum Discussion

Shahabh786's avatar
Shahabh786
Frequent Visitor
2 years ago
Solved

Help Needed: Finding Minimum Growth Value in Power BI

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!

 

Need Help
  • Shahabh786's avatar
    Shahabh786
    2 years ago

    I was able to finally achive this using below workaround.

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

3 Replies

  • Shahabh786's avatar
    Shahabh786
    Frequent Visitor
    2 years ago

    I was able to finally achive this using below workaround.

    ADDCOLUMNS (
                FILTER (
                    SUMMARIZE ( 'Sales', 'Calendar'[MonthYear] ),
                    [MonthYear] <> _CurrentMonthYear
                ),
                "@SPLM", [Monthly Growth %]
            )
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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