Aggregation issue.

[Kartikb]

Hi Guys,

I am trying to aggregate comp sales percentage by month, but it is not working properly. However, when I am aggregating the sales numbers they are aggregating properly. Please have a look at the screenshot below, any help would be appreciated.

Thanks

K

[v-frfei-msft]

Hi @Kartikb ,

Please create a measure using SUMX like that to work on it.

Measure = SUMX(Dimdate,[L4L Comp Sales %])

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[amitchandak]

Can you share your formula Sales %. I think you are looking for % of subtotal

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

Also, Create a sort column for you month year like

Month Year Sort = format(Date[Date],"YYYYMM")

And choose that as sort column

Appreciate your Kudos.

[Kartikb]

I want to find out average comp sales for the month.

Please see the formulas below:

1. L4L Comp Sales % =  DIVIDE([L4L Comp Sales],[L4L Sales LY],BLANK())

2. L4L Sales = CALCULATE

(

    SUM('vwDailyTransactions'[SaleExGST])

    , 'vwDailyTransactions'

     [IsValidL4L_TY]= True()

)

3. L4L Sales LY =  CALCULATE

(

    SUM('vwDailyTransactions'[SaleExGST])

    , FILTER(ALL('vwDimDate'), 'vwDimDate'[FYearWeek] = MAX('vwDimDate'[FYearWeek]) - 100)

)

Thank for the suggestion regarding the sort column.