Hi Lily and Diego
Not sure I am on the right track or not, anyways..
To have different format on same measure Field, I sometimes use this method.
Duplicate the data source (x3)
And assign each data source to respective measure.
Union from edit data source
Create calculated field
if [Table Name] ="Sheet1" and [Property] ="W" then float([Value])/86400 end
if [Table Name] ="Sheet11" and [Property]="X" then float([Value]) end
if [Table Name] ="Sheet11" and [Property]="Y" then float([Value]) end
if [Table Name] ="Sheet12" and [Property]="Z" then float([Value]) end
if attr([Table Name]) <>"Sheet1" then null else min([WWW])-lookup(min([WWW]),-1) end
[YYY / XXX]
sum([YYY]) / sum([XXX])
[YYY / XXX (YoY)]
[YYY / XXX]-lookup([YYY / XXX],-1)
[ZZZ (YoY) ]
if attr([Table Name]) <>"Sheet12" then null else sum([ZZZ])-lookup(sum([ZZZ]),-1) end
case [Table Name]
when "Sheet1" then "WWW"
when "Sheet11" then "YYY/XXX"
when "Sheet12" then "ZZZ"
I skip place holder format because it seems that you already know it.
Placeholder Pills_SM_10.2.twbx 29.3 KB
1 of 1 people found this helpful
I'm sorry, I forgot the above solution is the result of trials and errors and the file included unnecessary Data Pivot.
The concept is pretty similar but the workbook/formula should be like this new attachment as V2.
Diego, thank you for the ping.
I'm late to reply, pardon for that.
Besides, Shin had nailed this one already.
I'd like to add my 2c (as a pure exercise, of course :-) --
mainly trying to ease the view building part
at the expense of an over-complicated data prep.
So one could get a final view as a simple cross-tab --
having enough dimension values in the datasource
to "Scaffold" the view. The # of dim values should be
more than # of "Measure pairs" to place on a view.
In this case the [Property] dim has 4 distinct values,
and the "Measure pairs" on the view is equal to 3.
So I hide the 'X' value (reserved it for future use :-)
Please find the attached.
PS I've been caught the next moment after posting my reply
that the Join part is absolutely unnecessary, so left with Union x2 only.
Re-posting with the modified wb.
Placeholder Pills_YF.twbx 70.4 KB