Forum Discussion

ffunky's avatar
ffunky
Helper 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])

ffunky's avatar
ffunky
Helper I
7 years ago

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's avatar
    ffunky
    Helper I
    7 years ago

    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's avatar
      v-yulgu-msft
      Microsoft Employee
      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
        Helper I
        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])