October 1, 2015 at 6:05 pm #1915
- Started: 2
- Replies: 0
- Total: 2
Let’s start with a visual of our current data model (Within SSAS Tabular):
Current Data Model with One Core Dimension table combining (Company, Contract, Contract Item, Job, Phase, Cost Type)
Here is what our previous test data model looked like (this may help visual the data better):
Previous Test Data Model with Multiple smaller dimensions reflecting (Company, Contract, Contract Items, Job, Phase, Cost Type)
We were running into issues and a consultant recommended we create a single Dimension table JC Cost Revenue holding all the dimensional data. I still wonder whether we should have one larger JC Cost Revenue table (using BIDS Helper to create folders) or to have at least 6 dimensions for each area as you see in the second model. But that is not the focus of this post.
For this use case let’s look at how we look at our data when examining Revenue and Cost.
We normally look at our Job Cost Revenue data in the following way:
Contract (Revenue at Contract level)
Contract Item (Revenue at Item level)
Job Phase (Cost at Phase of Work level)
Cost Type (Cost at Cost Type level)
The Job / Phase / Cost Type can link to contract and contract items, BUT Contract and Contract Items do NOT link to a Job / Phase / Cost type.
So when you look at the Contract level you can determine the Billed Amount, but you can also get the Cost as all of the cost from Job / Phase / Cost Type are linked back to the contract / item levels.
However if you start out looking at Job / Phase / Cost Type you can’t get the Revenue as multiple Job / Phase / Cost Types could link to the same contract. It simply is not how you look at it. Revenue is only at the Contract / Contract Item levels
So when we are viewing the data in the structure above in a pivot table we can see the total Billed Amount and it is visible at each level including Job Phase Cost levels. We do NOT want the Billed Amount aggregate to be visible at the cost levels as it is meaningless.
So our first run at this was as follows:
<pre class=”lang:default decode:true crayon-selected”>Revenue Condition:=IF (
ISFILTERED ( ‘JC Cost Revenue'[JobPhaseCTID] )
|| ISFILTERED ( ‘JC Cost Revenue'[Job] )
|| ISFILTERED ( ‘JC Cost Revenue'[JobAndDescription] )
|| ISFILTERED ( ‘JC Cost Revenue'[Phase] )
|| ISFILTERED ( ‘JC Cost Revenue'[PhaseAndDescription] )
|| ISFILTERED ( ‘JC Cost Revenue'[CostType] )
|| ISFILTERED ( ‘JC Cost Revenue'[CostTypeAndDescription] ),
<pre class=”lang:default decode:true “>Billed Amount:=IF (
SUM ( ‘JC Revenue'[BilledAmt] ),
ALL ( ‘JC Cost Revenue'[CostType], ‘JC Cost Revenue'[Phase] )
Now at first go we think it looks good… I throw up rows Contract / Contract Item / Job / Phases / Cost Type and measures for BilledAmt and ActualCost. BUT if I have a slicer and I slice on Cost Type all of a sudden the Contract / Contract Item level no longer show the BilledAmt. Why? Due to the [Revenue Condition] and the fact it has:
<pre class=”lang:default decode:true”>ISFILTERED ( ‘JC Cost Revenue'[CostType] )
And of course if we remove this we get results we don’t want either.
So how the heck can we create a condition where BilledAmt will ONLY show at the Contract and Contract Item level? We are not using a hierarchy at this point, but may in the future.
Thanks in advance for your time.
GregOctober 2, 2015 at 12:33 pm #1922
- Started: 7
- Replies: 2566
- Total: 2573
I agree with your frustration, “How the heck can we create a condition…”. I have played around with a simplified model based on the current diagram and have arrived at the opinion that (for a single pivot using the current model) the answer is: you can’t because of how filters are flowing down from the JC Cost Revenue table combined with the filters defined within the measures.
Although you are paying a consultant a lot of money for their input, consider separating out contract and contract items into their own table (they were previously two tables, but if they can be combined within the JC Cost Revenue table, my thinking is that they could probably be combined into their own table that uses a combined field “ContractID” and “ContractItemID” as the new key for relationships). This will dis-associate “contract/contract item” filtering from “job/phase/cost type” filtering which I think is going to be the critical factor for solving the issue here.
As a completely separate approach, consider two pivots (something like a “Contract Information” pivot and a “Job Information” pivot).
You must be logged in to reply to this topic.