Semi Additive Measure as a Master Measure

ghaliabed — Mon, 27 Apr 2020 11:38:15 GMT

Hello

I am trying to figure out how to work with semi additive measures.

Have requirement to track the Number of Employees per entity and team over time.

I have in my data down to the level of the Employee and if they are present will be flagged

Sample Data:

YEAR	Quarter	YEARMONTH	ENTITY	TEAM	EMPLOYEE	EMPLOYEECOUNT
2019	1	201901	A	DEV	JOE	1
2019	1	201902	A	DEV	JOE	1
2019	1	201903	A	DEV	JOE	1
2019	2	201904	A	DEV	JOE	1
2019	2	201905	A	DEV	JOE	1
2019	1	201901	A	DEV	MIKE	1
2019	1	201902	A	DEV	MIKE	1
2019	1	201903	A	DEV	MIKE	1
2019	2	201905	A	DEV	MIKE	1
2019	1	201901	A	QA	SAM	1
2019	1	201902	A	QA	SAM	1
2019	1	201903	A	QA	SAM	1
2019	2	201904	A	QA	SAM	1

I want to have a master measure where users can create pivot tables and charts and be able to correctly see the latest Number of Present Employees based on their chosen dimensions.

Now if a user is always grouping the Sum(EmployeeCount) at the YEARMONTH level, they will get the correct value.

Ex: Pivot Table By Entity over YEARMONTH will give results like this

However, if user is doing any grouping without the YEARMONTH they will get incorrect results.

Ex: Pivot Table By Entity and Team over Quarters

The values here are wrong at Quarter level, where the correct values should be:

Q1: Show 3 instead of 9

Q2: Show 2 instead of 4

Is there a way to do this as a set expression and make it as a Master Measure?

Re: Semi Additive Measure as a Master Measure

ghaliabed — Mon, 11 May 2020 20:18:33 GMT

any ideas on this ?

Re: Semi Additive Measure as a Master Measure

Saravanan_Desingh — Tue, 23 Jun 2020 02:56:41 GMT

Are you looking something like this?

Re: Semi Additive Measure as a Master Measure

ghaliabed — Tue, 23 Jun 2020 11:40:25 GMT

Not exactly no.

If we remove the Month 201905 from pivot table, the Total for the DEV would still be 2.

But the correct value should actually be 1, since that is the value found in the Last available month

topic Re: Semi Additive Measure as a Master Measure in App Development

Semi Additive Measure as a Master Measure

Re: Semi Additive Measure as a Master Measure

Re: Semi Additive Measure as a Master Measure

Re: Semi Additive Measure as a Master Measure