what is that i am missing...need to calculate the %sales of year sales

[Raj007]

Hi All,

Thanks for your time.. i am just trying to understand with some sample data of adventure works.. i was able to follow and create the 

some metrics... like % of Grand Total sales - what is the sales percentage of each month when compared to the grand total

i was trying to do the same thing but for the year.. what is the sales percenatage of each month when compared to the TOTAL of the YEAR.. i used ALL and ALLEXCEPT..but i do not get the correct value

1).i have a date table

2).i have fact internet sales table

3).I have custom measures table..just for the measures only

Iam missing something in ALLEXCEPT

% of Year Sales Total = [vSalesAmount_Sumx1]/CALCULATE([vSalesAmount],ALLEXCEPT(dDate,dDate[Month]))

again, thanks a lot for your time...below is the screenshot of what i have

[amitchandak]

@Raj007 , Try like

% of Year Sales Total = divide([vSalesAmount_Sumx1], CALCULATE([vSalesAmount_Sumx1],filter(ALL('dDate'),'dDate'[Year]=max('dDate'[Year]))) )

[Raj007]

Hi Amit, thanks a lot,

% of Year Sales Total = [vSalesAmount]/CALCULATE([vSalesAmount],ALLEXCEPT(dDate,dDate[Year]))

 i get the same result as your expression.. i see you are using FILTER(ALL(table),filter expression)

why did we use the max? - if i use your expression - i see 6400% in grand total .. i think this can be fixed

just trying to understand..again thanks a lot

[amitchandak · Accepted solution]

@Raj007 , Try with all selected , I am trying to keep only year filter,

% of Year Sales Total = divide([vSalesAmount_Sumx1], CALCULATE([vSalesAmount_Sumx1],filter(ALLSELECTED('dDate'),'dDate'[Year]=max('dDate'[Year]))) )

make sure month and year in visual some from dDate table