Forum Discussion

cn4422's avatar
cn4422
Icon for Helper V rankHelper V
1 year ago
Solved

Calculating the Minimum Value Across Multiple Months

Hi,   I want to calculate the min-value for multiple months, so that for each month, the min-value reflects the actual minimum value across all selected months.   I currently have this formula fo...
  • sjoerdvn's avatar
    sjoerdvn
    1 year ago

    Ok, so it turns out that expanding the context within an "X" function get hideously complex. I got something that works though. Some extra notes: You really should replace the "Month" with a month-year column. Also, you should add an IF statementment to make sure not to return values outside the selected date range.

    Min Value = 
    MINX(
        SUMMARIZE(
            CALCULATETABLE(VALUES(DateTable[Month]),ALL(DateTable), TREATAS(ALLSELECTED(DateTable[Date_DateTable]),DateTable[Date_DateTable]))
                ,[Month]
                ,"Spend",VAR sm=[Month] RETURN CALCULATE([Spend FBA Switch], ALL(DateTable), DateTable[Month]=sm))
        ,[Spend]
    )
cn4422's avatar
cn4422
Icon for Helper V rankHelper V
1 year ago

I've created a PBI test-file

maybe this helps.

(It's a dropbox link - just open in a new tab - you don't need to register, you can download the file regardless).

 

I've also tried your 2 debugging measures and here's the result:

 

 

 

sjoerdvn's avatar
sjoerdvn
Icon for Solution Sage rankSolution Sage
1 year ago

Ok, so it turns out that expanding the context within an "X" function get hideously complex. I got something that works though. Some extra notes: You really should replace the "Month" with a month-year column. Also, you should add an IF statementment to make sure not to return values outside the selected date range.

Min Value = 
    MINX(
        SUMMARIZE(
            CALCULATETABLE(VALUES(DateTable[Month]),ALL(DateTable), TREATAS(ALLSELECTED(DateTable[Date_DateTable]),DateTable[Date_DateTable]))
                ,[Month]
                ,"Spend",VAR sm=[Month] RETURN CALCULATE([Spend FBA Switch], ALL(DateTable), DateTable[Month]=sm))
        ,[Spend]
    )
  • cn4422's avatar
    cn4422
    Icon for Helper V rankHelper V
    1 year ago

    sjoerdvn thanks again for your effort and the new Measure!

     

    This works pretty well. I've made some adjustments and I also implemented your tipps regarding "month-year-column" and the "IF-statement":

     

    CPL_FBA_Min =
    VAR SelectedMinDate = MINX(ALLSELECTED(Datum), Datum[DateAll])
    VAR SelectedMaxDate = MAXX(ALLSELECTED(Datum), Datum[DateAll])

    RETURN
        MINX(
            SUMMARIZE(
                CALCULATETABLE(
                    VALUES(Datum[Year-Month]),
                    ALL(Datum),
                    TREATAS(ALLSELECTED(Datum[DateAll]), Datum[DateAll])
                ),
                Datum[Year-Month],
                "CPL",
                VAR sm = Datum[Year-Month]
                RETURN CALCULATE(
                    [CPL FBA Base],
                    ALL(Datum),
                    Datum[Year-Month] = sm
                )
            ),
            IF(
                AND(
                    MIN(Datum[DateAll]) >= SelectedMinDate,
                    MAX(Datum[DateAll]) <= SelectedMaxDate
                ),
                [CPL],
                BLANK()
            )
        )

     

    ======

     

    And in combination with Min and Max I was then able to create the following conditional formatting:

     

    CPL_FBA_ColorScale =
    VAR CurrentCountry = SELECTEDVALUE(Countries[Country])
    VAR MinValue = CALCULATE([CPL_FBA_Min], ALL(Countries), Countries[Country] = CurrentCountry)
    VAR MaxValue = CALCULATE([CPL_FBA_Max], ALL(Countries), Countries[Country] = CurrentCountry)
    VAR CurrentValue = [CPL FBA Switch]
    VAR Range = MaxValue - MinValue

    RETURN IF(ISBLANK(CurrentValue), BLANK(), IF(Range = 0, 0.5, (CurrentValue - MinValue) / Range))
     
    ======
     
    So the end result is that I can now display different countries (X, Y, Z) with groups (A, B, C,...) where each group is formatted so that the maximum value across all months is red, the minimum value is green, and the values in between are yellow—based on each group/country itself.
     
    So, thanks again for your help! 🙂
     
    The only problem I have now is a performance issue... too many countries and groups in combination make conditional formatting for each group quite resource-intensive and push it to its performance limits... but at least it's working! 😁