Before I post my mock up and mask the data, I’m just curious if there actually is a way to add static values to a stacked chart. I have a bunch of a cost data (buckets that build up for construction activities ) at different locations across my X axis but want to simply add in the static value on top of the activity buckets. It’s an overhead cost compenent that will remain static.
Just curious if anyone has any ideas? I know the drill and will post it up when able but wanted to kick off the conversation in the meantime!
You’d probably have to give us a bit more detail, but say you wanted to have a static value of 100,000 you could create a measure which equals that value and add it into your stacked bar chart. My technique might be a bit blunt but I’m sure it would work. There are probably more elegant solutions.
Looking at the extensive tables in your model, I’ll show option 2
Note that I’ve replaced the Buckets in your visual you took from the Lem Entry table with those from the Bucket Sort dimension table.
and changed your measure to:
Direct Cost per Dig v2 =
IF( SELECTEDVALUE( 'Bucket Sort Table'[Order] ) =9 && SELECTEDVALUE( 'Planning and Progress Tracker'[ILI Tool Type] ) IN {"MFL-A", "MFL-C"},
50000, // replace this with a measure contaning the hard coded value
IFERROR( [Actual Cost] / [Dig Quantity (LEMS)], BLANK () ) + 0
)
This is really neat! It’s interesting, I lost the order of ‘Sort’ in my actual model as it went back to alphabetical. I think I’m missing a step that you did but can’t pin point it - see .png
Dramatically better! You are very close to a perfect star schema here, but you have one ticking bomb that you need to cut the red wire on. Relationships between dimension tables are a potential red flag, but particularly when they form a loop, which can lead to ambiguous paths which in turn can produce erratic, inaccurate DAX results.
gives me a lot of concern, since it potentially creates an ambiguous path between Planning and Progress and Lem Entry - potentially taking either the A OR B route.
Here are a couple of key documents to review regarding the implications of ambiguous paths (spoiler alert: they’re all really bad…)
The second one focuses primarily on ambiguity caused by bidirectional relationships, but ambiguity caused by other modeling problems has similar effects.
Once I think you get this taken care of though, you are in great shape with regard to your data model.
Hey @BrianJ thanks for the articles - I was attempting something w’ the use relationship function up there. I was trying to build out some cost (Lem entry) and (Budget) comparisons but the catch is I need to use the Dig Id from the Planning and Progress table since the Lem Entry cost data is reliant on dates from Planning and Progress. Obviously when I try to pull Budget info down it wont match up with my Cost @ the Dig Id level since that is coming from the Planning and Progress table.
I can make a separate post some day soon - don’t want to be the guy constantly posting questions!
That said, I do feel like I need an extra date table on top of Planning and Progress sometimes as well to bring more time intelligence into the model as I’m increasingly being asked for cost performance information based on dates in the Planning and Progress tracker.
A key concept here for getting the modeling exactly right is Expanded Tables. This is not an easy read, but as the tagline says understanding how expanded tables work in DAX is “of paramount importance” and it’s definitely worth the time and effort to go through it (multiple times for me…) until you feel comfortable with the key concepts.
A discussion for another day, and another thread but I wanted to pass this along to you:
There’s always more to learn, but you are making fantastic progress. Keep up the great work!
I appreciate the forum and great support very much. I can usually get things 90% there and then when I’ve exhausted my research, pop on here for the last push and someone always tops me up. Outstanding.
