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]
    )
sanalytics's avatar
sanalytics
Icon for Super User rankSuper 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
Icon for Helper V rankHelper 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
    Icon for Super User rankSuper 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
      Icon for Helper V rankHelper V
      1 year ago

      MarkLaf Thanks for your reply and your suggested solution!

       

      MarkLaf wrote:

      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 relationship:

      That's because of the simplied test-file. In the actual file I don't have columns with countries and spend which I could conntect. I get the data with a connector out of Facebook, for example, and there I only have campaigns that I can attribute to countries based on the campaign name.