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