Forum Discussion

ffunky's avatar
ffunky
Icon for Helper I rankHelper I
7 years ago
Solved

column total average vs row total

Hi all,   I have rates of sale by week and store, and I'm trying to get the column total to show an average, like the row totals do, but the column seems to only sum up the totals.  It's driving me...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi ffunky ,

     

    Please refer to this measure.

    Average Qty = AVERAGEX(SALES_WEEKLY,CALCULATE(SUM(SALES_WEEKLY[Null_Qty])))

    Best regards,

    Yuliana Gu

  • ffunky's avatar
    ffunky
    7 years ago

    A collegue here was able to find the solution to this.

     

    Measure = AVERAGEX(

                ADDCOLUMNS(                SUMMARIZE(SALES_WEEKLY,SALES_WEEKLY[STORE_NBR],SALES_WEEKLY[YR_WK_NBR]),"Test",CALCULATE(SUM(SALES_WEEKLY[Null_Qty]))),[Test])

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi!

 

you need to use "average per category" quick measure, enter to this link for see a example: AVERAGE SUBTOTAL.

 

 

ffunky's avatar
ffunky
Icon for Helper I rankHelper I
7 years ago

That gets my column totals corrected, but the row totals and grand totals are off now.

 

Null_Qty average per STORE_NBR =
AVERAGEX(
    KEEPFILTERS(VALUES('SALES_WEEKLY'[STORE_NBR])),
    CALCULATE(SUM('SALES_WEEKLY'[Null_Qty]))
)
  • ffunky's avatar
    ffunky
    Icon for Helper I rankHelper I
    7 years ago

    Sorry - that pic butchered the formatting.

     

     
    Null_Qty average per STORE_NBR = 
AVERAGEX(
	KEEPFILTERS(VALUES('SALES_WEEKLY'[STORE_NBR])),
	CALCULATE(SUM('SALES_WEEKLY'[Null_Qty]))
)