Forum Discussion

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(VALUES(FACT_SALES_ST[Business_type]); [S])

and the results are this,

So, i.e, in the second column, AVG, I have

2.189.255

5.800.164

17.143.370

25.132.789

same value for Net. well, that is incorrect, AVG for those values is 8.377.596,3

So, I need the value 8.377.596,3 (AVG) in the same column that Total

avg instead total..

thanks

Regards!

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

11 Replies

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_
  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
    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]
    )

Related content