Do not sum certain columns in matrix. Show values based on filter level?

[Andrew_SSFM]

Hello All!

I have some project data coming from two different tables and one of those tables (Project) has values (Fee, Complete) that should not be summed.  All values are attached to a 3-tier work breakdown structure of Project > Phase > Task.

This is what the data currently looks like and my desired result:

I would like the value for the top tier shown when not expanded.  When expanded, I only want to see the values of the lower tiers.

Is this possible?

I've been reading posts on parent-child hierarchies and path, but do not know how to use path when my data is already in a naturalized hierarchy:

WBS1	WBS2	WBS3	PM	Fee	StartDate	Org	PctComp
TEST00057.000			JOHN	266,600.00	7/21/2015	HN:CE:01	0.00
TEST00057.000	00CO1		JOHN	0.00	7/21/2015	HN:CE:01	0.00
TEST00057.000	02XX1		JOHN	25,000.00	7/21/2015	HN:CE:01	100.00
TEST00057.000	03XX1		JOHN	6,000.00	7/21/2015	HN:CE:01	96.00
TEST00057.000	04XX1		JOHN	26,000.00	7/21/2015	HN:CE:01	100.00
TEST00057.000	05XX1		JOHN	10,000.00	7/21/2015	HN:CE:01	100.00
TEST00057.000	06XX1		JOHN	0.00	7/21/2015	HN:CE:01	100.00
TEST00057.000	07XX1		JOHN	34,100.00	7/21/2015	HN:CE:01	98.00
TEST00057.000	09XX1		JOHN	70,000.00	7/21/2015	HN:CE:01	96.00
TEST00057.000	10XX1		JOHN	9,300.00	7/21/2015	HN:CE:01	100.00
TEST00057.000	11XX1		JOHN	13,000.00	7/21/2015	HN:CE:01	100.00
TEST00057.000	12XX1		VICTOR	0.00	7/21/2015	HN:CE:01	100.00
TEST00057.000	13XX1		JOHN	44,000.00	7/21/2015	HN:CE:01	0.00

Any assistance or guidance would be greatly appreciated.

Thank you!  

[Ashish_Mathur]

Hi,

You may download my PBI file from here.  I cannot get the Complete to show 0.  This should atleast be a start/guidance.

[d_gosbell · Accepted solution]

Actually if you fix the syntax errors in my previous code the same approach will work for both the fee and pcnt (assuming that the row with the blank WBS2 is a total row so it should be shown for the WB1 line and hidden as a WBS2 line

Fee Amt =

IF( HASONEVALUE(Data[WBS2])

   ,CALCULATE(SUM(Data[Fee]), FILTER(VALUES(Data[WBS2]),Data[WBS2] <> ""))

   ,CALCULATE(SUM(Data[Fee]),Data[WBS2] ="" )

)

[Andrew_SSFM]

Hey @d_gosbell , 

It does look like your measure is what I'm looking for!  Thank you for your help 🙂

[bachPBI]

Hi All! 

Is it possible to keep certain rows visible in a matrix but exclude their values in the matrix subtotals? Basically, I have an income statement with line items I do not want to include in totals but would like to keep their amounts visible in the matrix. Is this possible?

Thank you for any and all help!

[d_gosbell]

You could probably do this in a measure, using HASONEVALUE to detect if the current context is showing data drilled down to WBS2 or not and then either filter to only include or exclude the row with a blank WBS2 code. (I'm filtering based on an empty string, but if that does not work you might have to try using ISBLANK() depending on how your data is loaded)

Fee Amt = IF( HASONEVALUE(Project[WBS2]) 
      ,SUM(Project[Fee], FILTER(VALUES(Project[WBS2]) <> "")
     ,SUM(Project[Fee], FILTER(VALUES(Project[WBS2]) = "")  
)