Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
Hi,
You may download my PBI file from here. I cannot get the Complete to show 0. This should atleast be a start/guidance.
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
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!
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]) = "")
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |