Sorting 2nd dimension and share - a simple task without a solution

piter89 — Tue, 12 Mar 2024 08:09:14 GMT

Hello,

I have a problem with sorting the second dimension and the share of expanded rows in a pivot table. I had to use a calculated dimension for the second dimension. This gives me the ability to sort in the right order.
My data:

LOAD * Inline [ Dim1, Dim2, Value Germany, Diesel, 1200 Germany, Petrol, 1500 France, Diesel, 800 France, Petrol, 300 ]

My pivot table:

Dim1	=Aggr(Dual(Dim2, RowNo(TOTAL)), Dim1, Dim2)	Sum(Value)	Share
France		1100	28.95%
	Diesel	800	~~21,05%~~ 800/1100 = 72,73%
	Petrol	300	~~7,89%~~ 300/1100 = 27,27%
Germany		2700	71,05%
	Petrol	1500	...
	Diesel	1200	...

Unfortunately, after applying a calculated dimension, I have an issue with the row share. I can calculate the total market share, but I want to calculate the share of expanded rows in the parent row.

My expression for share measure:

If(Dimensionality() = 1, Sum(Value)/Sum(Total Value) , Sum(Value)/Sum(Total<Dim2> Value) )

This expression returns 100% for expanded rows. This works fine, if I change the calculated dimension to a normal dimension, but I lose the correct order of expanded rows.

Thanks in advance for your help.

Re: Sorting 2nd dimension and share - a simple task without a solution

LRuCelver — Tue, 12 Mar 2024 08:55:19 GMT

Column(1) / Pick(Dimensionality(), Sum(total Value), Only(Aggr(Sum(total <Dim1> Value), Dim1, Dim2)) )

topic Sorting 2nd dimension and share - a simple task without a solution in App Development

Sorting 2nd dimension and share - a simple task without a solution

Re: Sorting 2nd dimension and share - a simple task without a solution