Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have pivot table like below with 3 measure and one dimension
Measure | Dim | D1 | D11 | D12 | D2 | D21 | D22 |
M1 | 42 | 39 | 24 | 44 | 47 | 48 | |
M2 | 21 | 47 | 39 | 16 | 17 | 30 | |
M3 | 19 | 31 | 21 | 33 | 29 | 26 |
Now requirement is like,
when I select the D1, I just want to see D11,D12
when I select the D2, I just want to see D21,D22
Bydefault, I just want to see D1, D2
I think it will be very difficult to achieve this by doing selection from dimension itself as when you select any dimension value rest all value will be in excluded state. Try like below
f1:
load *,if(Match(Dim,'D1','D11','D12'),'D1',
if(Match(Dim,'D2','D21','D22'),'D2'))as NewDim inline [
Dim, M1,M2,M3
D1, 10,12,13
D11,13,10,34
D12,20,30,40
D2,10,34,5
D21,20,150,10
D22,23,45,67 ];
Create Pivot
Dimension:
Dim
Expressions:
M1-
=if(GetSelectedCount(NewDim)=0, sum({<Dim={'D1','D2'}>}M1),sum(M1))
M2-
=if(GetSelectedCount(NewDim)=0, sum({<Dim={'D1','D2'}>}M2),sum(M2))
M3-
=if(GetSelectedCount(NewDim)=0, sum({<Dim={'D1','D2'}>}M3),sum(M3))
hi,
FYI.
$@M.
Can you show sample output, Do you mean you have single dimension with values D1,D11,D12,D2,D21,D22?
How do you want to show Measure M1,M2,M3? and Values below Dim. Normally measure means aggregation formula, do you have aggregation like Sum,Avg for M1,M2,M3? More info will help.
You can use nested expressions and even variables. Then use them in conditional show of an expression .
Hi,
M1,M2,M3 having formula in pivot. and yes d1,d11... all are dimension values.. I have drag and drop the expression to come out with below format.
Sam,
I am using PE. Can you please show all the steps?
FYI.,
Please find the attached.
$@M.
I am not able to open. Can you please show the steps with screenshots?
DATA_TEMP:
CrossTable(DIM, VALUE)
LOAD Measure,
D1,
D11,
D12,
D2,
D21,
D22
FROM
datata.xlsx
(ooxml, embedded labels, table is Sheet1);
DATA:
LOAD
*,
if(Match(DIM,'D11','D12'),'D1',
if(Match(DIM,'D21','D22'),'D2')) as NEW_DIM
Resident DATA_TEMP;
DROP Table DATA_TEMP;
and
take a pivot table with dimension DIM & Measure.
expression sum(value)
take list box NEW_DIM,
and values from NEW_DIM.
$@M.