column total average vs row total

[ffunky]

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 crazy!  😉  

In my table, I replaced all zeros with Null so it calculates the rows correctly.

USW = averagex(SUMMARIZE(TABLE,TABLE[WK_NBR],"abc",sum(TABLE[Null_Qty])),[abc])

The column averages should be 3.1, 3.3, 2.9, 3.2, 3.1 (there are more stores than shown in pic)

[v-yulgu-msft · Accepted solution]

Hi @ffunky ,

Please refer to this measure.

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

Best regards,

Yuliana Gu

[ffunky]

Much closer now - but - I can't make it not include the null values in the calculation.  For instance, store 1474 should be 1.5, not 0.3.  Any ideas out there?

Null_Qty average per STORE_NBR =

sumx(

    KEEPFILTERS(VALUES('SALES_WEEKLY'[UPC_CD])),

    CALCULATE(sumx(SALES_WEEKLY,'SALES_WEEKLY'[Null_Qty])/count(SALES_WEEKLY[UPC_CD]))

)

[ffunky]

no ideas?  I'm still racking my brain on this one...  I'm at the point where bribery could be considered.  😉

This appears to be most accurate and colum totals at bottom are correct, but the total column needs to average the row, not sum them.

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

[v-yulgu-msft · Accepted solution]

Hi @ffunky ,

Please refer to this measure.

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

Best regards,

Yuliana Gu

[ffunky · Accepted solution]

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]

Hi!

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

[ffunky]

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]

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