cancel
Showing results for
Did you mean:

## Percentage on sales (Expense/Sales) - DAX Formula

Hi,

I need a formula to calculate and visualize percentage on sales. The following is my raw table:

 Code Date Category Amount A-0001 1-Jan Sales 800 A-0001 1-Jan Sales 300 X-0002 1-Jan Direct Cost 400 C-0003 1-Jan Expense 250 C-0003 1-Jan Expense 300 A-0001 21-Feb Sales 500 A-0001 21-Feb Sales 300 X-0002 21-Feb Direct Cost 400 C-0003 21-Feb Expense 100 C-0003 21-Feb Expense 100

Expected result Thank you.

Ardie

Status: Delivered v-yuezhe-msft
Moderator

`Month = MONTH(Table1[Date])`

Then create the following measure in your table, set category to  Expense and Direct Cost in visual level filter of your table visual.

`sale = CALCULATE(SUM(Table1[Amount]),FILTER(ALLEXCEPT(Table1,Table1[Month]),Table1[Category]="Sales"))`

`Measure = var selectedsale= CALCULATE(SUM(Table1[Amount]),FILTER(ALLEXCEPT(Table1,Table1[Month]),Table1[Category]="Sales")) return DIVIDE(SUM(Table1[Amount]),selectedsale)` Reagrds,
Lydia v-yuezhe-msft
Moderator
Status changed to: Delivered

Ardietrinidad Frequent Visitor

Hi Lydia,

Thank you for the idea but I have 1 issue, in the example I gave, this really works but when I apply to the real data I have, this calculates also the previous years. My requirement is when the month of January is selected and the year is 2019 in slicer, this shall calculate only for that specific period. Could you please help me out to figure out what is the right formula?

Is it posible that the filter can be 2 filters? Like Company code = XXXX and category = "Sales"?

Thank you.

Ardietrinidad Frequent Visitor

I got it wrong. The formula works perfectly. I realized that my problem is when I am using date table and taking my parameter there (slicer) for the month & year.

Is this not possible when I am using tme intelligence function? Like taking percentage on sales comparison from PY or Previous month vs Current selected date.

Thank you. v-yuezhe-msft
Moderator

You are able to use time intelligence function in this case, you can use Previousyear() and previousmonth() function to calculate the last year and last month sale.

Regards,
Lydia

Ardietrinidad Frequent Visitor

Hi Lydia,

Thank you for your response, I really appreciate it.

I tried but it didn't work. When there is a time intelligence function is required, the formula simply doesn't work. Could you please take a look help me out how the below requirement could possibly achieve?

Desired result: Raw data:

 Code Date Category Amount A-0001 01-Jan-2018 Sales 850 A-0001 01-Jan-2018 Sales 450 X-0002 01-Jan-2018 Direct Cost 500 C-0003 01-Jan-2018 Expense 250 C-0003 01-Jan-2018 Expense 300 A-0001 01-Jan-2019 Sales 800 A-0001 01-Jan-2019 Sales 300 X-0002 01-Jan-2019 Direct Cost 400 C-0003 01-Jan-2019 Expense 250 C-0003 01-Jan-2019 Expense 300 A-0001 21-Feb-2019 Sales 500 A-0001 21-Feb-2019 Sales 300 X-0002 21-Feb-2019 Direct Cost 400 C-0003 21-Feb-2019 Expense 100 C-0003 21-Feb-2019 Expense 100

Thanks.

Ardie v-yuezhe-msft
Moderator

Create the following columns in your table.

`Year = YEAR(Table4[Date])`
`Month = MONTH(Table4[Date])`

Then create the following measures in  your table.

`sale = var selectedyear=SELECTEDVALUE(Table4[Year]) var selectedmonth=SELECTEDVALUE(Table4[Month]) return CALCULATE(SUM(Table4[Amount]),FILTER(ALL(Table4),Table4[Category]="Sales"&&DATE(Table4[Year],Table4[Month],1)=DATE(selectedyear,selectedmonth,1)))`
`Measureper = DIVIDE(SUM(Table4[Amount]),[sale])`

`lastyearsale = var selectedyear=SELECTEDVALUE(Table4[Year])-1 var selectedmonth=SELECTEDVALUE(Table4[Month]) return CALCULATE(SUM(Table4[Amount]),FILTER(ALL(Table4),Table4[Category]="Sales"&&DATE(Table4[Year],Table4[Month],1)=DATE(selectedyear,selectedmonth,1)))`

`lastcategory = var selectedyear=SELECTEDVALUE(Table4[Year])-1 var selectedmonth=SELECTEDVALUE(Table4[Month]) return CALCULATE(SUM(Table4[Amount]),FILTER(ALLEXCEPT(Table4,Table4[Category]),DATE(Table4[Year],Table4[Month],1)=DATE(selectedyear,selectedmonth,1)))`
`lastyearper = DIVIDE([lastcategory],[lastyearsale])`

Create table visual and use year & month slicer to filter the visual, also set category in visual level filters. Reagrds,
Lydia

Completed Ideas