Dashboard showing aggregates when I need individual amounts due to between time slider

[tmendoza]

Hello All,

I've got a data set that has categories in a column such as Net Revenue, Gross Margin, Head Count etc. and the amount to these in a separate column. There is also a column for the period that these amounts sit in.

So my data set is in such order: Periods, Categories, Amount.

I'm displaying a vertical barchart as a visual to express the amounts. I'm using a between periods slicer to select desired periods. I've got another category slicer that shows all the categories I mentioned above. The categories slicer is on single select, so everytime I pick a category, the vertical bar chart changes to show the amount of the category selected.

 These work fine for individual periods, but when my slider is picking between two periods things go a little hay wire. Let's say I pick April - September. Everyting in the categories other than Head Count are fine, because the chart is aggregating the amounts and that's fine. However, Head Count aggregates all the employes counted in our company at the end of each month and I get a very large number that is significantly more than the employees we had at the end of September.

So that's what I'm looking for, a method that will allow me to only show the amount of employees at the end of the month for HC while aggregating the amounts for all the other categories that are selected when the Period slicer is picking between two periods.

Any Ideas?

[sturlaws · Accepted solution]

Hi @tmendoza,

you would need to do something like 

headCount =
CALCULATE (
    COUNT ( 'Table'[EmployeeID] );
    FILTER (  'Table' ; 'Table'[period] = MAX ( 'Table'[period] ) )
)

 cheers,
S

[tmendoza]

Worked like a charm!!!!

Except, instead of using COUNT, I used SUM to aggregate the head count in the MAX Period.

My final Equation was:

Actual Value = 
IF(
          SELECTEDVALUE('Category Group Tbl'[Category Group])= "HC",
             CALCULATE(SUM(GL[Actual Amount]),FILTER(GL,GL[Period] = MAX('Period Tbl'[Period]))),
SUM(GL[Actual Amount])
)

THANK YOU!!!!!!! THANK YOU!!!!! THANK YOU!!!!!! THANK YOU!!!!!!!