Forum Discussion

fullcount's avatar
fullcount
Frequent Visitor
4 years ago
Solved

Auto-selecting from MAXX

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 e...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    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

amitchandak's avatar
amitchandak
Super User
4 years ago

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

fullcount's avatar
fullcount
Frequent Visitor
4 years ago

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

 

Forecast MethodProjected SalesActual SalesAccuracy
Method 1900

1000

90.0%
Method 2950100095.0%
Method 3975100097.5%
Method 4925100092.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's avatar
    v-easonf-msft
    Community Support
    4 years ago

    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