Forum Discussion

Joymuststudy's avatar
Joymuststudy
Regular Visitor
5 years ago

Calculating MoM % Change using measures

We are trying to calculate the Month-over-month employee type % change in Power BI, spent a few days but still no luck, anyone can help?

 

Raw data:

We have the following raw data table for each month (Jan, Feb, Mar, etc..), the Manager, Employee and Employee Type columns will change every month.

Department

Manager

Employee Name

Employee Type

Marketing

A

Jack

Full-Time

Marketing

A

Jims

Contractor

Marketing

A

John

Full-Time

Marketing

B

Alice

Full-Time

Marketing

B

Andrew

Outsource

HR

A

Celine

Full-Time

HR

A

Candy

Full-Time

HR

D

Catherine

Full-Time

HR

D

Cathy

Full-Time

HR

D

Cat

Full-Time

HR

E

Caca

Full-Time

HR

F

Cici

Contractor

HR

G

Apple

Contractor

Production

H

Fufu

Contractor

Production

I

Floral

Outsource

Production

J

Funny

Outsource

Tech

K

Fan

Outsource

Tech

L

Gina

Outsource

Tech

M

Gigi

Outsource

Tech

M

Gugu

Full-Time

Tech

M

Gogo

Contractor

Marketing

A

Mimi

Outsource

HR

A

Mumu

Full-Time

HR

D

Mama

Full-Time

HR

E

Mmmm

Full-Time

HR

F

Jaja

Contractor

HR

G

Juju

Contractor

Production

H

Jojo

Contractor

Production

I

Jiji

Outsource

Production

J

Wuwu

Outsource

Tech

K

Wawa

Outsource

Tech

L

Wiwi

Outsource

Tech

M

Wwww

Outsource

Tech

M

Qiqi

Full-Time

Tech

M

Ququ

Contractor

 

What we have now:

a table (visualization) showing the total # of employees by employee type(rows) and by months (columns)

 

Jan

Jan

Feb

Feb

Employee Type

#

%

#

%

Full-time

 

 

 

 

Contractor

 

 

 

 

Outsource

 

 

 

 

We also have a slicer for Manager and Department, right now numbers in the above table will change if we change our selection in the slicer.

 

Questions:

How do we get MoM % change by each category(employee type)? and we want the result dynamic (results change if we change selection in slicer - Manager and Department)

 

Jan

Jan

Feb

Feb

Employee Type

#

%

#

%

Full-time

 

*

 

*

Contractor

 

*

 

*

Outsource

 

*

 

*

 

What we tried before (but doesnt work):

create a summarized table each each unique (employee type+department+manager), but it doesnt work because sum of % change in each cateory doesnt equal to the overall % change.

 

Any help will be appreciated! thanks : )

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Joymuststudy , I am not seeing any date and month column. In any case, I would like to have date column, or convert month to: month start or end date.

    And then will try to use time intelligence

     

    example

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
    last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

     

    diff = [MTD Sales]-[last MTD Sales]
    diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

     

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.