Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Translate SMAPE Calculation from Tableau to Power BI

Trying to get this calculation in Tableau to work in BI, but im stuck for a while now. In Tableau it's like:

{INCLUDE  [Product Key]:  2*ABS(SUM([Constr Demand])-SUM([Actual Qty]))/SUM(ABS([Constr Demand])+ABS([Actual Qty]))}

What I've tried is many different DAX sollutions but i'm not getting the correct value. If i split them up, i get the same vales in voth parts, but Tableau sums each row, then averages the value, and thats what messing it up for me.
My latest trial doesnt give much values back but the logic is sort of correct compared to tableau.

 

#SMAPE Prod = 
    AVERAGEX(
        SUMMARIZE(
            df_backtest_day,df_backtest_day[product_key],
            "Sum_2Times",
            2*ABS(
                SUM(df_backtest_day[constr_demand])-SUM(df_backtest_day[actual_qty])
            )
            /
            ABS(
                SUMX(df_backtest_day,df_backtest_day[constr_demand]+df_backtest_day[actual_qty]
                )
            )
        ),[Sum_2Times]
    )

 

Need Help
  • Fowmy's avatar
    Fowmy
    6 years ago

    Anonymous 

    Try the following formula and replace your table name and column. I made assumption of your table

     

    SMAPE Prod = 
AVERAGEX(
    SUMMARIZE(
        'Table',
        'Table'[key],            
        "Sum_2Times",            
        DIVIDE(            
                2*ABS(SUM('Table'[demand])-SUM('Table'[actual])),            		  
                    ABS(
                    SUMX(
                        'Table',
                        'Table'[demand]+'Table'[actual]
                    )
                )
        )
    ),
    [Sum_2Times]
)

     

    ________________________

    Did I answer your question? Mark this post as a solution, this will help others!.

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube, LinkedIn