Forum Discussion

ivancornejo_'s avatar
ivancornejo_
Frequent Visitor
8 years ago
Solved

AVG instead Total in matrix

Hi all,    I need know if I can change the Total Value for AVG, but in the same column that totals are showing. I've try with these formula  1.-  S = SUM(FACT_SALES_ST[Net]) 2.- AVG = AVERAGEX(V...
Need Help
  • popov's avatar
    popov
    8 years ago

    Hello, ivancornejo_
    I tested new formula and get needed result.

    AVG =
    AVERAGEX (
        ADDCOLUMNS (
            SUMMARIZE ( FACT_SALES_ST; DIM_PRODUCT_ST[Product_division] );
            "Net Value"; CALCULATE ( SUM ( FACT_SALES_ST[Net] ) )
        );
        [Net Value]
    )

popov's avatar
popov
Resolver III
8 years ago

Hello!

Try this formula

AVG :=
AVERAGEX (
    ADDCOLUMNS (
        SUMMARIZE ( FACT_SALES_ST; FACT_SALES_ST[Business_type] );
        "Net Value"; CALCULATE ( SUM ( FACT_SALES_ST[Net] ) )
    );
    [Net Value]
)

ivancornejo_'s avatar
ivancornejo_
Frequent Visitor
8 years ago

Really close! popov
thanks for quick answer 

with your formual I get this. I have AVG but for row Total. 

 

so, basically, need this 

 

In "blue" row, I need the AVG, for each column

Hope you understand 

 

  • popov's avatar
    popov
    Resolver III
    8 years ago
    Try it:
    AVG :=
    AVERAGEX (
    ADDCOLUMNS (
    SUMMARIZE ( FACT_SALES_ST; FACT_SALES_ST[Business_type]; FACT_SALES_ST[Product_division]);
    "Net Value"; CALCULATE ( SUM ( FACT_SALES_ST[Net] ) )
    );
    [Net Value]
    )
    • ivancornejo_'s avatar
      ivancornejo_
      Frequent Visitor
      8 years ago

      popov, Thanks a lot! 

      It looks fantastic! 

      but (sorry), now have this    

       

      columns MKTG, RETAIL AND WHOLESALE are good, but Total Column show AVG for each row (accesories, apparel and footwear)
      you can see de differences with this, in Total column is a SUM and Total row is the AVG 

       

      Thanks again! 

       

      • popov's avatar
        popov
        Resolver III
        8 years ago

        Could you share a sample file which can reproduce the issue?