Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I have a pie chart with pivot table as its fast change type.
I need to display only the positive values both in the Pie chart as well as pivot table.
my data appears as folows:
Subscription Type | Sales Amount |
---|---|
one time | 39000 |
subscription | -2563 |
So i need to display only the value of Subscription Type=One time in my chart neglecting the Subscription Type=subscription.
My value in % calculation exceeds more than 100% in both charts.
May be i can use RELATIVE to make it 100% in pie chart but how could i do that in pivot table?
Thanks in advance!
Hi,
Please Find the Attachment
Thanks
Manju
Percentage of what? of all sales?
you use set analysis to calculate the one time subscriptions and then you put that in relation to all sales (or whatever you want it to relate to).
sum({$<[Subscription Type]={'one time'}>}[Sales Amount])
/
sum(total [Sales Amount])
Alternate way if you have more fieldvalues for Subscription Type...
=sum({$<[Subscription Type]-={'subscription'}>}[Sales Amount])
Hi,
Just do one while loading data itself ignore the negative values in script
like
Temp:
LOAD * INLINE [
Sub Type, sales_Amount
one time, 39000
subscription, -2563
];
Inline:
NoConcatenate
load
*
Resident Temp where sales_Amount>=0;
DROP Table Temp;
and finally your front end expression will be like this
=sum(sales_Amount)/sum(TOTAL sales_Amount) and make into percentage in number tab
Thanks
Manju
Hope this will help you in neglecting negative values for subscription types..
just add a calculated dimension instead of adding "Subscription Type" as:
=if([Sales Amount]>=0,[Subscription Type])
and check "Suppress when value is null" for the same dimension you have just added
Now check.. it works..
Thanks and regards,
UI