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

3 Replies

  • 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