Hi All, I have versioned roster data, and I get this by employee. E.g. Everyones roster is published (at differing times) and the data flows through. If a change is then made to someones roster, I get their whole roster of data again with the change. The changes are timestamped (and versioned as a quick reference). Basic example below. Employee NumberFirst NameSurnameRoster GroupResource GroupBaseDateActivityLayover LocationRosterRoster VersionOccurred When001BobJonesTestUnder TrainingAKL20/03/2020WORK 2020.03114/03/2020 13:11001BobJonesTestUnder TrainingAKL20/03/2020LAYOVERBHE2020.03114/03/2020 13:11001BobJonesTestUnder TrainingAKL21/03/2020WORK 2020.03114/03/2020 13:11001BobJonesTestUnder TrainingAKL22/03/2020OFF 2020.03114/03/2020 13:11001BobJonesTestUnder TrainingAKL20/03/2020WORK 2020.03220/03/2020 18:57001BobJonesTestUnder TrainingAKL20/03/2020LAYOVERWLG2020.03220/03/2020 18:57001BobJonesTestUnder TrainingAKL21/03/2020WORK 2020.03220/03/2020 18:57001BobJonesTestUnder TrainingAKL21/03/2020LAYOVERCHC2020.03220/03/2020 18:57001BobJonesTestUnder TrainingAKL22/03/2020WORK 2020.03220/03/2020 18:57002JohnSmithTestUnder TrainingWLG20/03/2020OFF 2020.03114/03/2020 13:13002JohnSmithTestUnder TrainingWLG21/03/2020OFF 2020.03114/03/2020 13:13002JohnSmithTestUnder TrainingWLG21/03/2020OFF 2020.03114/03/2020 13:13002JohnSmithTestUnder TrainingWLG20/03/2020OFF 2020.03214/03/2020 14:26002JohnSmithTestUnder TrainingWLG21/03/2020WORK 2020.03214/03/2020 14:26002JohnSmithTestUnder TrainingWLG21/03/2020WORK 2020.03214/03/2020 14:26002JohnSmithTestUnder TrainingWLG20/03/2020WORK 2020.03314/03/2020 17:40002JohnSmithTestUnder TrainingWLG21/03/2020WORK 2020.03314/03/2020 17:40002JohnSmithTestUnder TrainingWLG21/03/2020WORK 2020.03314/03/2020 17:40 So at any given time, employee 1 might be up to version 4 but employee 2’s latest roster is still their first roster version because there are no changes. My ultimate vision is to step through these change timestamps and show change as time progresses which I’ll do with the play axis or similar. At each timestamp I want to get each employees latest roster version, and then make calculations on each employees latest roster to arrive at a roster total. For example total hotel nights. In the screenshots below, each row of the table is correct (I guess only because I’m displaying the table by employee), but the total is wrong because at the shown timestamp employee 2 has the biggest roster version and they have no hotel nights. How do I get the measure to evaluate for each employee as I step through timestamps? Appreciate all help and advice *Latest Roster Version = CALCULATE(MAX('Roster Data'[Roster Version]),FILTER('Roster Data','Roster Data'[Occurred When]<=SELECTEDVALUE('Change Timestamp Summary'[Occurred When])))*Hotel Nights = VAR LatestRosterVersion = [*Latest Roster Version] RETURN CALCULATE(COUNTROWS('Roster Data'),'Roster Data'[Layover Location]<>"",'Roster Data'[Roster Version]=LatestRosterVersion)

Forum Discussion

Frequent Visitor

6 years ago

Solved

Calculating totals based on data subsets

Hi All,

I have versioned roster data, and I get this by employee. E.g. Everyones roster is published (at differing times) and the data flows through. If a change is then made to someones roster, I get their whole roster of data again with the change. The changes are timestamped (and versioned as a quick reference). Basic example below.

Employee Number	First Name	Surname	Roster Group	Resource Group	Base	Date	Activity	Layover Location	Roster	Roster Version	Occurred When
001	Bob	Jones	Test	Under Training	AKL	20/03/2020	WORK		2020.03	1	14/03/2020 13:11
001	Bob	Jones	Test	Under Training	AKL	20/03/2020	LAYOVER	BHE	2020.03	1	14/03/2020 13:11
001	Bob	Jones	Test	Under Training	AKL	21/03/2020	WORK		2020.03	1	14/03/2020 13:11
001	Bob	Jones	Test	Under Training	AKL	22/03/2020	OFF		2020.03	1	14/03/2020 13:11
001	Bob	Jones	Test	Under Training	AKL	20/03/2020	WORK		2020.03	2	20/03/2020 18:57
001	Bob	Jones	Test	Under Training	AKL	20/03/2020	LAYOVER	WLG	2020.03	2	20/03/2020 18:57
001	Bob	Jones	Test	Under Training	AKL	21/03/2020	WORK		2020.03	2	20/03/2020 18:57
001	Bob	Jones	Test	Under Training	AKL	21/03/2020	LAYOVER	CHC	2020.03	2	20/03/2020 18:57
001	Bob	Jones	Test	Under Training	AKL	22/03/2020	WORK		2020.03	2	20/03/2020 18:57
002	John	Smith	Test	Under Training	WLG	20/03/2020	OFF		2020.03	1	14/03/2020 13:13
002	John	Smith	Test	Under Training	WLG	21/03/2020	OFF		2020.03	1	14/03/2020 13:13
002	John	Smith	Test	Under Training	WLG	21/03/2020	OFF		2020.03	1	14/03/2020 13:13
002	John	Smith	Test	Under Training	WLG	20/03/2020	OFF		2020.03	2	14/03/2020 14:26
002	John	Smith	Test	Under Training	WLG	21/03/2020	WORK		2020.03	2	14/03/2020 14:26
002	John	Smith	Test	Under Training	WLG	21/03/2020	WORK		2020.03	2	14/03/2020 14:26
002	John	Smith	Test	Under Training	WLG	20/03/2020	WORK		2020.03	3	14/03/2020 17:40
002	John	Smith	Test	Under Training	WLG	21/03/2020	WORK		2020.03	3	14/03/2020 17:40
002	John	Smith	Test	Under Training	WLG	21/03/2020	WORK		2020.03	3	14/03/2020 17:40

So at any given time, employee 1 might be up to version 4 but employee 2’s latest roster is still their first roster version because there are no changes. My ultimate vision is to step through these change timestamps and show change as time progresses which I’ll do with the play axis or similar.

At each timestamp I want to get each employees latest roster version, and then make calculations on each employees latest roster to arrive at a roster total. For example total hotel nights.

In the screenshots below, each row of the table is correct (I guess only because I’m displaying the table by employee), but the total is wrong because at the shown timestamp employee 2 has the biggest roster version and they have no hotel nights.

How do I get the measure to evaluate for each employee as I step through timestamps?

Appreciate all help and advice

*Latest Roster Version = CALCULATE(MAX('Roster Data'[Roster Version]),FILTER('Roster Data','Roster Data'[Occurred When]<=SELECTEDVALUE('Change Timestamp Summary'[Occurred When])))

*Hotel Nights = 
VAR LatestRosterVersion = [*Latest Roster Version]
RETURN
CALCULATE(COUNTROWS('Roster Data'),'Roster Data'[Layover Location]<>"",'Roster Data'[Roster Version]=LatestRosterVersion)

Need Help

v-lid-msft

6 years ago

Hi ScottA ,

We can use the following measure by using SUMX function to meet your requirement.

*Hotel Nights =

SUMX (

    VALUES('Roster Data'[Employee Number]),

    VAR LatestRosterVersion = [*Latest Roster Version]

    RETURN

        CALCULATE (

            COUNTROWS ( 'Roster Data' ),

            'Roster Data'[Layover Location] <> "",

            'Roster Data'[Roster Version] = LatestRosterVersion

        )

)

And here is the result:

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Calculating totals based on data subsets.pbix53 KB

3 Replies

ScottA

Frequent Visitor

6 years ago

Hi All,

Employee Number	First Name	Surname	Roster Group	Resource Group	Base	Date	Activity	Layover Location	Roster	Roster Version	Occurred When
001	Bob	Jones	Test	Under Training	AKL	20/03/2020	WORK		2020.03	1	14/03/2020 13:11
001	Bob	Jones	Test	Under Training	AKL	20/03/2020	LAYOVER	BHE	2020.03	1	14/03/2020 13:11
001	Bob	Jones	Test	Under Training	AKL	21/03/2020	WORK		2020.03	1	14/03/2020 13:11
001	Bob	Jones	Test	Under Training	AKL	22/03/2020	OFF		2020.03	1	14/03/2020 13:11
001	Bob	Jones	Test	Under Training	AKL	20/03/2020	WORK		2020.03	2	20/03/2020 18:57
001	Bob	Jones	Test	Under Training	AKL	20/03/2020	LAYOVER	WLG	2020.03	2	20/03/2020 18:57
001	Bob	Jones	Test	Under Training	AKL	21/03/2020	WORK		2020.03	2	20/03/2020 18:57
001	Bob	Jones	Test	Under Training	AKL	21/03/2020	LAYOVER	CHC	2020.03	2	20/03/2020 18:57
001	Bob	Jones	Test	Under Training	AKL	22/03/2020	WORK		2020.03	2	20/03/2020 18:57
002	John	Smith	Test	Under Training	WLG	20/03/2020	OFF		2020.03	1	14/03/2020 13:13
002	John	Smith	Test	Under Training	WLG	21/03/2020	OFF		2020.03	1	14/03/2020 13:13
002	John	Smith	Test	Under Training	WLG	21/03/2020	OFF		2020.03	1	14/03/2020 13:13
002	John	Smith	Test	Under Training	WLG	20/03/2020	OFF		2020.03	2	14/03/2020 14:26
002	John	Smith	Test	Under Training	WLG	21/03/2020	WORK		2020.03	2	14/03/2020 14:26
002	John	Smith	Test	Under Training	WLG	21/03/2020	WORK		2020.03	2	14/03/2020 14:26
002	John	Smith	Test	Under Training	WLG	20/03/2020	WORK		2020.03	3	14/03/2020 17:40
002	John	Smith	Test	Under Training	WLG	21/03/2020	WORK		2020.03	3	14/03/2020 17:40
002	John	Smith	Test	Under Training	WLG	21/03/2020	WORK		2020.03	3	14/03/2020 17:40

At each timestamp I want to get each employees latest roster version, and then make calculations on each employees latest roster to arrive at a roster total. For example total hotel nights.

In the screenshots below, each row of the table is correct (I guess only because I’m displaying the measure by employee in the table), but the total is wrong because at the shown timestamp employee 2 has the biggest roster version and they have no hotel nights.

How do I get the measure to evaluate for each employee as I step through timestamps?

Appreciate all help and advice

*Latest Roster Version = CALCULATE(MAX('Roster Data'[Roster Version]),FILTER('Roster Data','Roster Data'[Occurred When]<=SELECTEDVALUE('Change Timestamp Summary'[Occurred When])))

*Hotel Nights = 
VAR LatestRosterVersion = [*Latest Roster Version]
RETURN
CALCULATE(COUNTROWS('Roster Data'),'Roster Data'[Layover Location]<>"",'Roster Data'[Roster Version]=LatestRosterVersion)

v-lid-msft
Community Support
6 years ago
Hi ScottA ,

We can use the following measure by using SUMX function to meet your requirement.

*Hotel Nights = SUMX ( VALUES('Roster Data'[Employee Number]), VAR LatestRosterVersion = [*Latest Roster Version] RETURN CALCULATE ( COUNTROWS ( 'Roster Data' ), 'Roster Data'[Layover Location] <> "", 'Roster Data'[Roster Version] = LatestRosterVersion ) )

And here is the result:

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Calculating totals based on data subsets.pbix53 KB
- ScottA
  Frequent Visitor
  6 years ago
  Hi v-lid-msft
  
  This is perfect, thank you. I can see how VALUES() is creating a distinct list/table of employee_numbers, and then SUMX is allowing the original measure to SUM for each row of that table.
  
  I had been experimenting with SUMX but I just couldn't visualise how to get it to work.
  
  Really appreciate it, cheers

Forum Discussion

Calculating totals based on data subsets

3 Replies

Related content

Calculate total of subset based on slicer

Subset of data

Calculating Percentage of Total of a subset

Total value missing in calculated column

Cumulative Total