Calculate percentage in pivot table based max row value

[GopalBh]

Hi, 

I have a data with pivot table as 

Dimensions:

row - status   

column - date & values

measures

count(status)

status	date	values
	count(status)	%
Inq created	500
Inq Closed	10	2%
Inq response	300	60%
Inq conf	200	40%

I want to have % column. Here I am calculating % against "Inq created".    i.e (Inq Closed/Inq created) --> (20/500)*100 = 2%

Please help.

[justISO]

Hi, maybe something like this could work? if you put new measure with set analysis always divide from 'Inq created' value:

count(status) / count( total {<status={'Inq created'}>} status)

[GopalBh]

Hi,

Thank you for that. I get percentage, but not the correct percentage value. In my example:

I have 

Inq Closed: 12

Inq Created: 711

Inq Response: 699

But I get % as 1%, 45% and 44% respectively. Actually % for "Inq Response" should be (699/711)*100 = 98%.

[justISO]

hmm, I created a little sample like yours:

test_data:
load * inline [
status, data
Inq Created, 1,
Inq Created, 2,
Inq Created, 3
Inq Created, 4,
Inq Created, 5,
Inq Created, 6,
Inq Created, 7,
Inq Closed, 1,
Inq Response, 1,
Inq Response, 2,
Inq Response, 3,
Inq Response, 4,
Inq Response, 5,
Inq Response, 6 ];

and result in pivot table seems ok for me with the same formula I provided before:

[GopalBh]

Hi,

Thank you for that. It works when I remove Date from column. Because Date has more than one day day or if I keep data for only one day, then the result is good.

How is there is data for more than one day in the column

[brunobertels]

Hi 

may be this 

count(status) / count( total {<status={'Inq created'},Date=>} status)

[GopalBh]

Please see the output in Pivot

[justISO]

try with aggregation per date:

Count(status) / Aggr(nodistinct Count({<status={'Inq Created'}>}status), date)

[GopalBh]

Hi, Perfect. Thank you very much for the help.