Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a concatenated fact table with two fact contexts and corresponding dimensions:
FactName | CargoID | CargoGroup | PathName | DateID | DamageTypeName |
---|---|---|---|---|---|
Transport | 1 | Package | A-->B | 20180101 | |
Transport | 1 | Package | B-->C | 20180201 | |
Transport | 1 | Package | C-->D | 20180301 | |
Transport | 2 | Envelope | A-->B | 20180201 | |
Transport | 2 | Envelope | B-->D | 20180301 | |
Damage | 1 | Package | A-->B | 20180101 | Chipped |
Damage | 1 | Package | A-->B | 20180101 | Scratched |
Damage | 2 | Envelope | A-->B | 20180101 | Crushed |
I calculate the measure Count({<FactName = {'Transport'}, DamageTypeName =>} distinct CargoID) in a pivot table with dimensions DamageTypeName and CargoGroup. The result looks as follows:
Edit: Problem here is the fact context "Transport" does not have dimension values for DamageTypeName. Nevertheless I want calculate the total value for number of transported cargo to show for each DamageTypeName.
What I want it to look like is:
Attached is an example, which I used to test different behaviours.
Does anyone have an idea?
Kind regards,
Andy
I believe this might be what you are looking for then:
You don't need the dimension CargoGroup in the Pivot Table.
Define three measures:
Expression: Count(TOTAL {<CargoGroup={*}>} distinct CargoID)
Label: Gesamtwerte
Expression: Count({<FactName={'Damage'},CargoGroup={'Package'}>} DISTINCT CargoID)
Label: Package
Expression: Count({<FactName = {'Damage'},CargoGroup={'Envelope'}>} DISTINCT CargoID)
Label: Envelope
or with a single measure and keeping the CargoGroup dimension:
Expression:
If( ColumnNo()=0, // column 0 is the Totals column
Count(TOTAL {<FactName = {'Damage'}>} DISTINCT CargoID)
,Count({<FactName = {'Damage'}>} DISTINCT CargoID)
)
Hello Petter,
thanks for your response. But I actually need the total measure from FactName = {'Transport'} to show for the dimension DamageTypeName. Problem here is, that the dimension has only values for fact context "Damage".
I edited the original post to clarify this.