Calculating the Minimum Value Across Multiple Months

[cn4422]

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]

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]
    )
)

[cn4422]

@sjoerdvn  Thanks - I'll definitely check this out!

[sanalytics]

@cn4422 

Use below pbix file for your solution.

May i ask why Country dimension is disconnected.?

Regards

sanalytics

[cn4422]

@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]

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]

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]

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]

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 · Accepted solution]

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]

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

[sanalytics]

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]

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]

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]

@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.

[sanalytics]

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

[FarhanJeelani]

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]

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.