Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

0

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:

 

CodeDateCategoryAmount
A-00011-JanSales800
A-00011-JanSales300
X-00021-JanDirect Cost400
C-00031-JanExpense250
C-00031-JanExpense300
A-000121-FebSales500
A-000121-FebSales300
X-000221-FebDirect Cost400
C-000321-FebExpense100
C-000321-FebExpense100

 

Expected result

 

Capture.PNG

 

Thank you.

Ardie

Status: Delivered
Comments
v-yuezhe-msft
Employee

@Ardietrinidad ,

Please create the following column in your table.

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)


1.PNG


Reagrds,
Lydia

 

v-yuezhe-msft
Employee
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
Employee

@Ardietrinidad ,

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:

 

Untitled.png

 

 

 

 

 

Raw data:

 

CodeDateCategoryAmount
A-000101-Jan-2018Sales850
A-000101-Jan-2018Sales450
X-000201-Jan-2018Direct Cost500
C-000301-Jan-2018Expense250
C-000301-Jan-2018Expense300
A-000101-Jan-2019Sales800
A-000101-Jan-2019Sales300
X-000201-Jan-2019Direct Cost400
C-000301-Jan-2019Expense250
C-000301-Jan-2019Expense300
A-000121-Feb-2019Sales500
A-000121-Feb-2019Sales300
X-000221-Feb-2019Direct Cost400
C-000321-Feb-2019Expense100
C-000321-Feb-2019Expense100

 

Thanks.

Ardie

v-yuezhe-msft
Employee

@Ardietrinidad ,

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.

1.png
Reagrds,
Lydia

Ardietrinidad
Frequent Visitor

Hi Lydia,

 

Everything works perfectly fine but I have additional requirement related to this. 

 

PI.png