Forum Discussion
Do not sum certain columns in matrix. Show values based on filter level?
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! :smileyhappy:
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] ="" ))
5 Replies
- d_gosbell
Super User
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]) = "")
) - 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
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] ="" ))
