ISINTOTAL not working

[mmills2018]

Hello,

I have a hierarchy based on Platform and Group.  Group is the lowest level of the hierarchy.  I need to bring in static data for my goals, when i bring this data in, my matrix table is adding the subtotals.  So, my subtotals are at the platform level.  I tried using ISINSCOPE to customize my subtotals, but for some reason i can't get it to work.  below is my measure and data.  any ideas what i am doing wrong?

measure:

var _group = ISINSCOPE(Sheet1[Group])

var _groupsum = sum(Sheet1[Goal])

var platform = ISINSCOPE(Sheet1[Platform])

var platformsum = CALCULATE(sum(Sheet1[Goal]),Sheet1[IsPlatform]=1)

return

SWITCH(TRUE(),_group,_groupsum,platform,platformsum)

Data:

Platform	Group	Goal	Mgmt Levels	IsPlatform
Platform 1		1.1	Group Total	1
Platform 1		2.1	Total PME	1
Platform 1		3.1	M4+	1
Platform 1		4.1	M1-M3	1
Platform 1		5.1	P1-P6	1
Platform 1		6.1	O1-O4	1
Platform 1		7.1	B1-B4	1
Platform 1		8.1	T1-T4	1
Platform 1	Group 1	8.1	Group Total	0
Platform 1	Group 1	8.1	Total PME	0
Platform 1	Group 1	8.1	M4+	0
Platform 1	Group 1	8.1	M1-M3	0
Platform 1	Group 1	8.1	P1-P6	0
Platform 1	Group 1	8.1	O1-O4	0
Platform 1	Group 1	8.1	B1-B4	0
Platform 1	Group 1	8.1	T1-T4	0
Platform 1	Group 2	0.1	Total PME	0
Platform 1	Group 2	2.1	M4+	0
Platform 1	Group 2	3.1	M1-M3	0
Platform 1	Group 2	4.1	P1-P6	0
Platform 1	Group 2	5.1	O1-O4	0
Platform 1	Group 2	6.1	B1-B4	0
Platform 1	Group 2	7.1	T1-T4	0
Platform 2		8.1	Total PME	1
Platform 2		10.1	M4+	1
Platform 2		11.1	M1-M3	1
Platform 2		12.1	P1-P6	1
Platform 2		13.1	O1-O4	1
Platform 2		14.1	B1-B4	1
Platform 2		15.1	T1-T4	1
Platform 2	Group 3	8.1	Total PME	0
Platform 2	Group 3	10.1	M4+	0
Platform 2	Group 3	11.1	M1-M3	0
Platform 2	Group 3	12.1	P1-P6	0
Platform 2	Group 3	13.1	O1-O4	0
Platform 2	Group 3	14.1	B1-B4	0
Platform 2	Group 3	15.1	T1-T4	0

[MFelix · Accepted solution]

Hi @mmills2018 ,

What is the expected result for this calculation how is the sub-totals expected to be calculated.

Lookking at your data you need to pick up all the value for the platform, since you are using the inscope for both the values believe that the error you are getting in on the total line correct that is not returning any value for this you can simple use the measure below:

measure = var _group = ISINSCOPE(Sheet1[Group])
var _groupsum = sum(Sheet1[Goal])
var platform = ISINSCOPE(Sheet1[Platform])
var platformsum = CALCULATE(sum(Sheet1[Goal]),Sheet1[IsPlatform]=1)
return
SWITCH(TRUE(),_group,_groupsum,platformsum)

However I have some doubts about your syntax since when making the hierarchy like you have you get a value with blanks(line highlithed) so when you filter that out your calculation get's incorrect you can remake you measure to:

oupsum = sum(Sheet1[Goal])
var platform = ISINSCOPE(Sheet1[Platform])
var platformsum = CALCULATE(sum(Sheet1[Goal]),FILTER(ALL(sheet1),Sheet1[IsPlatform]=1 && sheet1[Platform] in VALUES(sheet1[Platform])))
return
SWITCH(TRUE(),_group,_groupsum,platformsum)

This will allow to filter out the blanks and get the correct resutls:

Another option can go to having dimension tables for the platforms and groups with the objectives on those tables but that implies big changes in your model.

Check PBIX file attach.