Forum Discussion

cn4422's avatar
cn4422
Helper 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 for the FBA Switch:

 

FBA Switch =
 SWITCH(SELECTEDVALUE(Countries[Country]),
"AT",DIVIDE([Spend FBA AT],[Leads FBA AT],0),
"BE",DIVIDE([Spend FBA BE],[Leads FBA BE],0))
 
 
and this formular for the Min-Value:

MinValue Visible =
VAR CurrentCountry = SELECTEDVALUE(Countries[Country])
RETURN
CALCULATE(
    MINX(
        FILTER(
            ALLSELECTED(Datum[Month]),
            NOT ISBLANK([FBA Switch])
        ),
        [FBA Switch]
    ),
    FILTER(ALL(Countries), Countries[Country] = CurrentCountry)
)
 
However, the result is that the min-value always equals the FBA Switch value for the current month, when it should instead return the minimum value across all the months.
 
 
 
Any input on how to fix this is much appreciated!
 
Thx! 
 
  • 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]
    )

17 Replies

  • FarhanJeelani's avatar
    FarhanJeelani
    Super User
    1 year ago

    Hi cn4422 ,

    Your current DAX formula for MinValue Visible is calculating the minimum within the current month instead of considering all selected months. The issue is likely caused by the ALLSELECTED(Datum[Month]) filter, which does not ensure that all months remain in scope correctly.

    Corrected DAX Measure
    Try modifying your MinValue Visible measure as follows:

    MinValue Visible =
VAR CurrentCountry = SELECTEDVALUE(Countries[Country])
RETURN
CALCULATE(
MIN([FBA Switch]),
ALLSELECTED(Datum),
FILTER(ALL(Countries), Countries[Country] = CurrentCountry)
)


    Explanation of Fix:
    ALLSELECTED(Datum): Ensures that all months in the selected range remain in context.
    MIN([FBA Switch]): Now correctly calculates the minimum value across all selected months for the same country.
    FILTER(ALL(Countries), Countries[Country] = CurrentCountry): Maintains the filter on the selected country.


    Expected Outcome
    Now, for each row (month), the MinValue Visible column should show the same minimum value across all selected months for that country instead of just repeating FBA Switch for that specific month.

     

    Please mark this post as solution if it helps you. Appreciate Kudos.

    • cn4422's avatar
      cn4422
      Helper V
      1 year ago

      Hi FarhanJeelani ,

      thanks for your reply and your explanation.

       

      When I use your measure I get the following error message:

      The ‘MIN’ function only accepts a column reference as argument no. 1.

       

      That's why I tried MINX.

  • sanalytics's avatar
    sanalytics
    Super User
    1 year ago

    Hello cn4422 
    You can use below any of the measure for min value.

    Min Value = 
CALCULATE(
    MIN( 'Table'[FBA Switch] ),
    ALLEXCEPT('Table','Table'[Country] )
)
    Min Value 2 = 
CALCULATE(
    MIN( 'Table'[FBA Switch] ),
    ALL( 'Table' ),
    VALUES( 'Table'[Country] )
)
    Min Value 3 = 
CALCULATE(
    MIN( 'Table'[FBA Switch] ),
WINDOW(1,ABS,1,REL,
SUMMARIZE( ALLSELECTED( 'Table'), 'Table'[Country], 'Table'[Monat] ),
ORDERBY( SUM('Table'[FBA Switch]),ASC), PARTITIONBY( 'Table'[Country] )
) )

     

    Below screenshots

     Hope it helps

    Let me know if you need pbix file.

     

    Regards

    sanalytics

     

  • sanalytics's avatar
    sanalytics
    Super User
    1 year ago

    Hello cn4422 
    You can use below any of the measure for min value.

    Min Value = 
CALCULATE(
    MIN( 'Table'[FBA Switch] ),
    ALLEXCEPT('Table','Table'[Country] )
)
    Min Value 2 = 
CALCULATE(
    MIN( 'Table'[FBA Switch] ),
    ALL( 'Table' ),
    VALUES( 'Table'[Country] )
)
    Min Value 3 = 
CALCULATE(
    MIN( 'Table'[FBA Switch] ),
WINDOW(1,ABS,1,REL,
SUMMARIZE( ALLSELECTED( 'Table'), 'Table'[Country], 'Table'[Monat] ),
ORDERBY( SUM('Table'[FBA Switch]),ASC), PARTITIONBY( 'Table'[Country] )
) )

     

    Below screenshots

     Hope it helps

    Let me know if you need pbix file.

     

    Regards

    sanalytics

     

    • cn4422's avatar
      cn4422
      Helper V
      1 year ago

      Hi sanalytics ,

       

      thanks for your reply!

       

      Unfortunately, it doesn't work and I think it's because [FBA Switch] is a measure and not in a table.

       

      I have created a Test-Power BI file where I inserted the test-data.

       

      Test-File

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

       

      Maybe you could have a look?

       

      Thanks!

      • MarkLaf's avatar
        MarkLaf
        Super User
        1 year ago

        First, clean up your Spend/Country measures. What you are doing with [Spend FBA AT], [Spend FBA BE], [Spend FBA CH], and [Spend FBA Switch] we usually achieve with relationships:

         

        1) create a relationship between Countries -1--M-> Spend (you already have dates related to spend, we are doing the same here)

        2) now you can just have the following measure that automatically is filtered by Countries in your visuals:

        Spend FBA = SUM( Spend[Spend] )

         

         

        Now, you can do something like this to get the min spend by country:

         

        Spend FBA Country Min = 
VAR _iter = 
GENERATE( 
    VALUES( Countries[Country] ), 
    ALLSELECTED( DateTable[Date_DateTable].[Monat] ) 
)
RETURN
MINX( 
    _iter,
    VAR _thisMo = [Date_DateTable].[Monat] RETURN
    IF( 
        NOT ISEMPTY( Spend ), 
        CALCULATE( 
            [Spend FBA], 
            DateTable[Date_DateTable].[Monat] = _thisMo, 
            REMOVEFILTERS( DateTable ) 
        ) 
    )
)

         

    • cn4422's avatar
      cn4422
      Helper V
      1 year ago

      sanalytics Thanks for your help!


      sanalytics wrote:

      May i ask why Country dimension is disconnected.?

      Sure! 🙂

      The reason is that I have provided a simplified Test-file... in the real file there isn't an actual country dimension because I get the country-data from campaigns running on for example Facebook. On Facebook I have the countries segmented with campaign-names, like [AT]Campaign_1 for a campaign running in Austria.

       

      And I have created a switch-statement:

      Countries = DATATABLE(
          "Country", STRING,
          "Country Name", STRING,
          {
              {"AT", "Österreich"},
              {"BE", "Belgien"},
              {"CH", "Schweiz"}
          }
      )
      which I then use in combination with data from the Facebook-campaigns, like ad spend to create a Matrix-Table like this:
       
       
      And therefore I wanted to have the Min- and Max-Values which I can then use for conditional formatting. 🙂
       
  • sjoerdvn's avatar
    sjoerdvn
    Solution Sage
    1 year ago

    Hi,
    Had another look into it, and it turns out it can actually be a bit less complex. Also, the "IF" to supress results outside the date range in scope is best implemented at the highest level, so not inside the MINX.

    IF (AND(
        MIN(DateTable[Date_DateTable])<=MAXX(ALLSELECTED(DateTable[Date_DateTable]),[Date_DateTable]),
        MAX(DateTable[Date_DateTable])>=MINX(ALLSELECTED(DateTable[Date_DateTable]),[Date_DateTable])
    ),
    MINX(
        SUMMARIZE(
            CALCULATETABLE(ALLSELECTED(DateTable[Month]))
                ,[Month]
                ,"Spend",VAR sm=[Month] RETURN CALCULATE([Spend FBA Switch], ALL(DateTable), DateTable[Month]=sm))
        ,[Spend]
    )
)
  • sjoerdvn's avatar
    sjoerdvn
    Solution Sage
    1 year ago

    I don't think you need any logic around country. Try this to expand the date context:

     

    MinValue Visible =
    MINX(CALCULATETABLE(VALUES(Datum[Month]),ALL(Datum), ALLSELECTED(Datum[Month])),
        [FBA Switch]
    )

     

    • cn4422's avatar
      cn4422
      Helper 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
        Solution 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])