How to make a measure to show percentage used for each department

[Kiran_Gundu]

Hi,

1)I am trying to calculate percentages increases by Employee NUmber in Monthly level then its working fine.

Percentage Change =
VAR LastMonthSalary =
CALCULATE(
SUM('Sheet1'[SAL]),
FILTER(
'Sheet1',
'Sheet1'[EMPNO] = EARLIER('Sheet1'[EMPNO]) &&
'Sheet1'[MONTH].[MonthNo] = EARLIER('Sheet1'[MONTH].[MonthNo])-1)
)
Var ThisMonthSalary = 'Sheet1'[SAL]
RETURN IF(NOT ISBLANK(LastMonthSalary),DIVIDE( (ThisMonthSalary- LastMonthSalary) , LastMonthSalary))

2)Trying to calculate depart ment wise percentages but not calculating correctly.

Data :

Month         Sal                Department

January        31000             10

Febrauary    35500              10

March          40300              10

January       49000               20

Febrauary   56500                20

March          64500               20

January       45000               30

Febrauary   54000               30

March        63600                30

Expected result

Month           Sal            Dep          Previous          Percentages

Jan               31000         10                                                 

Feb              35500         10            31000                14%

March          40300         10             35500               13%

Same as for DEpartment 20 and 30.

Thanks

[Phil_Seamark · Accepted solution]

Hi @Kiran_Gundu

Try using calculated measures instead.

I've uploaded a PBIX file wiht three calculated measures added

Sum of Sal = SUM('Table1'[Sal])

Sum of Sal Prevous Month = 
   CALCULATE(SUM('Table1'[Sal]),PREVIOUSMONTH(('Dates'[Date])))

Sal Percentage Change = DIVIDE([Sum of Sal]-[Sum of Sal Prevous Month],[Sum of Sal])

You'll note I've added Date table and using the Month field from that in my DAX calculations (and on the axis of visuals)

[Phil_Seamark]

Hi @Kiran_Gundu

I copied your code in as a calculated column to my model and it worked perfectly.  I have attached a copy of the model

[Kiran_Gundu]

Please find the below data, you can paste it in xls.

MONTH EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1/1/2018 7839 KING PRESIDENT 17-Nov-81 8000 10
1/1/2018 7698 BLAKE MANAGER 7839 1-May-81 9000 30
1/1/2018 7782 CLARK MANAGER 7839 9-Jun-81 15000 10
1/1/2018 7566 JONES MANAGER 7839 2-Apr-81 11000 20
1/1/2018 7788 SCOTT ANALYST 7566 19-Apr-87 8000 20
1/1/2018 7902 FORD ANALYST 7566 3-Dec-81 7000 20
1/1/2018 7369 SMITH CLERK 7902 17-Dec-80 14000 20
1/1/2018 7499 ALLEN SALESMAN 7698 20-Feb-81 10000 300 30

Requirement.

Include (emp lvl)
1) Add slicer for emp name
2) Single Line chart.. that will show salary vs. month
3) Single Line chart.. that will show salary % increased vs. month

Include (dept lvl)
1) Add slicer for Dept name
2) Single Line chart.. that will show Sum of Salary of Dept vs. month
3) Single Line chart.. that will show salary % increased of Dept vs. month

Include (Month lvl)
1) Add slicer for Month
2) Single Line chart.. that will show Sum of Salary of Month
3) Single Line chart.. that will show salary % increased Month ON month

1/1/2018 7521 WARD SALESMAN 7698 22-Feb-81 9000 500 30
1/1/2018 7654 MARTIN SALESMAN 7698 28-Sep-81 4000 1400 30
1/1/2018 7844 TURNER SALESMAN 7698 8-Sep-81 7000 0 30
1/1/2018 7876 ADAMS CLERK 7788 23-May-87 9000 20
1/1/2018 7900 JAMES CLERK 7698 3-Dec-81 6000 30
1/1/2018 7934 MILLER CLERK 7782 23-Jan-82 8000 10
2/1/2018 7839 KING PRESIDENT 17-Nov-81 9500 10
2/1/2018 7698 BLAKE MANAGER 7839 1-May-81 10500 30
2/1/2018 7782 CLARK MANAGER 7839 9-Jun-81 16500 10
2/1/2018 7566 JONES MANAGER 7839 2-Apr-81 12500 20
2/1/2018 7788 SCOTT ANALYST 7566 19-Apr-87 9500 20
2/1/2018 7902 FORD ANALYST 7566 3-Dec-81 8500 20
2/1/2018 7369 SMITH CLERK 7902 17-Dec-80 15500 20
2/1/2018 7499 ALLEN SALESMAN 7698 20-Feb-81 11500 500 30
2/1/2018 7521 WARD SALESMAN 7698 22-Feb-81 10500 1000 30
2/1/2018 7654 MARTIN SALESMAN 7698 28-Sep-81 5500 2500 30
2/1/2018 7844 TURNER SALESMAN 7698 8-Sep-81 8500 3000 30
2/1/2018 7876 ADAMS CLERK 7788 23-May-87 10500 20
2/1/2018 7900 JAMES CLERK 7698 3-Dec-81 7500 30
2/1/2018 7934 MILLER CLERK 7782 23-Jan-82 9500 10
3/1/2018 7839 KING PRESIDENT 17-Nov-81 11100 10
3/1/2018 7698 BLAKE MANAGER 7839 1-May-81 12100 500 30
3/1/2018 7782 CLARK MANAGER 7839 9-Jun-81 18100 1000 10
3/1/2018 7566 JONES MANAGER 7839 2-Apr-81 14100 2500 20
3/1/2018 7788 SCOTT ANALYST 7566 19-Apr-87 11100 3000 20
3/1/2018 7902 FORD ANALYST 7566 3-Dec-81 10100 20
3/1/2018 7369 SMITH CLERK 7902 17-Dec-80 17100 20
3/1/2018 7499 ALLEN SALESMAN 7698 20-Feb-81 13100 30
3/1/2018 7521 WARD SALESMAN 7698 22-Feb-81 12100 30
3/1/2018 7654 MARTIN SALESMAN 7698 28-Sep-81 7100 30
3/1/2018 7844 TURNER SALESMAN 7698 8-Sep-81 10100 30
3/1/2018 7876 ADAMS CLERK 7788 23-May-87 12100 20
3/1/2018 7900 JAMES CLERK 7698 3-Dec-81 9100 30
3/1/2018 7934 MILLER CLERK 7782 23-Jan-82 11100 10

[Kiran_Gundu]

I am trying to get previous value for that data but it was calculating three times. I am trying to create measure but getting an error in earlier function so created column value.

LastMonthSalary =
CALCULATE(
[Total Sal],
FILTER(
'Sheet1',
'Sheet1'[DEPTNO] = EARLIER('Sheet1'[DEPTNO]) &&
'Sheet1'[MONTH].[MonthNo] = EARLIER('Sheet1'[MONTH].[MonthNo])-1)
)

Result:

Month  Dept            Sal                     Previous_value

Jan         10           31000                 

Feb         10          35500                   93000(31000*3) calculated but expected 31000

Mar         10         40300                   106500(35500*3) calculated but expected 35500

same as calculating other departments.

[Phil_Seamark · Accepted solution]

Hi @Kiran_Gundu

Try using calculated measures instead.

I've uploaded a PBIX file wiht three calculated measures added

Sum of Sal = SUM('Table1'[Sal])

Sum of Sal Prevous Month = 
   CALCULATE(SUM('Table1'[Sal]),PREVIOUSMONTH(('Dates'[Date])))

Sal Percentage Change = DIVIDE([Sum of Sal]-[Sum of Sal Prevous Month],[Sum of Sal])

You'll note I've added Date table and using the Month field from that in my DAX calculations (and on the axis of visuals)

[Kiran_Gundu]

Super sir, but it will not show april month as not contains april data in our data. how can we restrict without using filters.

[Phil_Seamark · Accepted solution]

Hi @Kiran_Gundu

One approach is to only return it only if you have a value for the previous month.

eg..  Update your measure to :

Sum of Sal Prevous Month = 
    IF(
        NOT ISBLANK([Sum of Sal]),
        CALCULATE(SUM('Table1'[Sal]),PREVIOUSMONTH(('Dates'[Date])))
    )

[Kiran_Gundu]

Thank you Sir..

[Kiran_Gundu]

Hi Phil,

I think its worked  when we given data independently. Actually I am using Employeed data to calculate department wise perecentages, when I draged sal column then it was giving summarized data by department wise. I think its the problem. can you please try to add employee number column to that and give salaries instead of summarized department salaries.

Thanks,

[Phil_Seamark]

Hi @Kiran_Gundu

Any chance you can please mock up a sample set of data in Excel and paste it here with some expected results?