cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ivancornejo_
Frequent Visitor

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, 

n1.png

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! 

 

 

 

 

1 ACCEPTED SOLUTION

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

View solution in original post

11 REPLIES 11
popov
Resolver III
Resolver III

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

Really close! @popov
thanks for quick answer 

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

n3.png

 

so, basically, need this 

 

n2.png

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

Hope you understand 

 

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

@popov, Thanks a lot! 

It looks fantastic! 

but (sorry), now have this    

 n4.png

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 

n2.png

 

Thanks again! 

 

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

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

View solution in original post

dadadance
Frequent Visitor

Hi @popov ,

 

I have used your formula, but I am still getting SUM instead of AVG.

Any ideas?

AVG =
AVERAGEX(
ADDCOLUMNS(
SUMMARIZE(product_per_store,'calendar'[Date].[Month]),"Net Value",[dayZ]),[Net Value])
 
avg_instead_of_sum.png
Thanks!

Hello, @dadadance,

You need to use column in table rows instead calendar'[Date].[Month])

Fantastic @popov ! 

 

Thanks a lot 🙂 

@ivancornejo_ 

HI 

can u share the link of sample PBIX file. I have same senario.

 

Thanks 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors