WxShady13
Board Regular
- Joined
- Jul 24, 2018
- Messages
- 185
- Office Version
- 365
- Platform
- Windows
- Mobile
Code:
HeadcountbyMonth =
CALCULATE(
SUM(TermCategoryTurnoverPercentage[Headcount]),
ALLSELECTED(TermCategoryTurnoverPercentage[Category]),
Filter(
ALLSELECTED(TermCategoryTurnoverPercentage[Date]),
TermCategoryTurnoverPercentage[Date]>=MIN(TermCategoryTurnoverPercentage[Date]) &&
TermCategoryTurnoverPercentage[Date]<=Max(TermCategoryTurnoverPercentage[Date])
),
VALUES(DATE(YEAR(TermCategoryTurnoverPercentage[Date]), MONTH(TermCategoryTurnoverPercentage[Date]),1))
)
I have a table that has Company (Category), Term Category (Involuntary/Voluntary) and Hourly/Salaried. I need to create a measure that will total the headcount by whatever criteria is selected by the various slicers I have on the page (i.e. Date range, Category, Term Category or Hourly/Salaried). The data table is setup similar to what is shown below. I have duplicates for month/company because I need to have a row for Hourly/Involuntary, Hourly/Voluntary, Salaried/Involuntary and Salaried/Voluntary. So another challenge is to divide by 2 the headcount by month. Any help is greatly appreciated,
Category | Term Category | Year | Term Month | Date | Hourly/Salaried | Total | Headcount |
Company X | Involuntary | 2024 | Jan | 1/1/2024 | Hourly | 15 | 2386 |
Company X | Involuntary | 2024 | Jan | 1/1/2024 | Salaried | 35 | 1185 |
Company X | Voluntary | 2024 | Jan | 1/1/2024 | Hourly | 99 | 2386 |
Company X | Voluntary | 2024 | Jan | 1/1/2024 | Salaried | 10 | 1185 |