Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a pivot table as shown below. I need the sales in the sales column, but in the total row, i need only the sum of products having flag=x. Can anyone suggest some expression for the same.
Product | Flag | Sales |
A | x | 10 |
B | x | 20 |
C | y | 40 |
Total | 30 |
If we write an expression for sales as sum(sales) where flag is minimum, the total row will consider only flag=x and I will be able to get the sales across each product as well.
Any suggestions for the expression?
Regards,
Pollisetti
Try something like:
if(dimensionality()=0, sum({<Flag = {'x'}>}Sales), sum(Sales))
Hope this helps!
=sum(<flag={'x'}>sales)
The value wouldn't show for the 'y' flag, but the others should show 10 and 20 respectively with a total of 30 for the 2nd expression column
Try something like:
if(dimensionality()=0, sum({<Flag = {'x'}>}Sales), sum(Sales))
Hope this helps!
I want the value to be shown for Y as well but not to be considered in total
can u suggest me the expression to calculate:
sum(flag=min(flag) sales)
Something like:
sum({<Flag={$(=min(Flag))}>}Sales)
I can think of a lengthy workaround.
1. Create expression containing the basic sales - Expr1
= sales // Total here becomes null
2. Create second expression - Expr2
= sum(if(min(all flag) = flag,sales)) // Total here is sum of sales where flag is min
3. Create third expression which is based on first two
= if( Expr2 = 0, Expr1, Expr2) // This will give the column that you need
4. Hide the first two expressions.(option available in Presentation tab of chart properties)