Auto-selecting from MAXX

[fullcount]

Hello,

I have several forecast methods that predict outcomes with varying accuracy depending on the item.  I want to blend these into a single forecast measure, which will choose the method for each item that has the highest accuracy.

I think this will take three steps.  The first step is working, which is to find the max accuracy among the methods:

Optimized Forecast =

VAR MaxAcc = CALCULATE(MAXX('Forecast Methods',[4WK Projection Accuracy]))

The second step needs to get me from this numeric accuracy value to the name of the Forecast Method itself.  I have tried a few different approaches, including to make a CALCULATETABLE where the Forecast Methods are summarized and [4WK Projection Accuracy] is returned as a column, but I can't seem to get it to work.

The third step needs to get me from the name of the Forecast Method to that method's [Volume Forecast].  Perhaps I don't need a second step, although it would be nice to know which methods are working best for which items.

Thanks for any help you can offer,

Ben

[amitchandak]

@fullcount ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[fullcount]

Here's a simplified, tabular version of what's going on in the [Projection Accuracy] measure:

Forecast Method	Projected Sales	Actual Sales	Accuracy
Method 1	900	1000	90.0%
Method 2	950	1000	95.0%
Method 3	975	1000	97.5%
Method 4	925	1000	92.5%

[Optimized Forecast]:= CALCULATE(MAXX('Forecast Methods' Table, [Projection Accuracy]))

[Optimized Forecast] would return 97.5% as written right now. 

What I referred to as step two in the original post is that I'd like to know that Method 3 was the most accurate one.  And step three would be to return the [Projected Sales] measure (or anything else attached to the Forecast Method column.

Thanks for any advice you can provide,

Ben

[v-easonf-msft · Accepted solution]

Hi,  @fullcount 

In your second step, you can try calculated table formula as below:

Calcualted table = 
VAR M1 = [Measure1] // replace with your own measure formulas
VAR M2 = [Measure2]  
VAR M3 = [Measure3]
VAR M4 = [Measure4]
VAR tab1 =
    DATATABLE (
        "Forecast Method", STRING,
        {
            { "Method1" },
            { "Method2" },
            { "Method3" },
            { "Method4" }
        }
    )
RETURN
    ADDCOLUMNS (
        tab1,
        "Measure column",
            SWITCH (
                [Forecast Method],
                "Method1", M1,
                "Method2", M2,
                "Method3", M3,
                "Method4", M4
            )
    )

Then you can apply your original measure normally：

Optimized result = MAXX('Calcualted table','Calcualted table'[Measure column])

Best Regards,
Community Support Team _ Eason