cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

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

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

1 ACCEPTED SOLUTION
Super User

@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

3 REPLIES 3
Super User

@Raj007 , Try like

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

Helper III

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
Super User

@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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors