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

Hi sjoerdvn ,

 

thanks for your reply!

 

I've tried your measure. MinValue Visible is returning the values for FBA Switch for each month, but not the actual min-value over all selected months for a particular country, which would bei 41,72 for AT and 11,44 for BE.

 

 

 

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

There's no way to tell without more info: what are the columns you are using in that visual? what slicers are in place? what's the definition of the meaures used within the "FBA SWITCH" ?

 

You can try adding these measure below to see where it fails, they both should return "3". If the 1st one doesn't the issue is in the date context. If the 2nd doesn't the issue is in that measure.

Debug1 = COUNTROWS(CALCULATETABLE(VALUES(Datum[Month]),ALL(Datum), ALLSELECTED(Datum[Month]))

Debug2 = COUNTX(CALCULATETABLE(VALUES(Datum[Month]),ALL(Datum), ALLSELECTED(Datum[Month])),[FBA Switch])

 

  • 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! 😁