1 2 Previous Next 21 Replies Latest reply on Feb 5, 2017 11:36 PM by koushik.neelakantha

# Difference between subcategory WRT Sales

Hello Everyone,

Lets say Phones is having most Sales Values and its value is 330007,

Chairs did 328449 but as difference needs to be shown in graph, it should be (330007-328449= 1558)

Likewise storage  did 223844 but to show in graph it should be 330007-223844 = 106163

Tables did 206966 but to show in graph it should be 330007-206966 = 123041

Regards,

Koushik.

• ###### 1. Re: Difference between subcategory WRT Sales

Hi Koushik,

Use quick table calculation (difference) on your sales measure.

Compute using Sub-Category

Relative to "Phone" sub-category

Once done it will look like this.

Let us know If this help.

Mahfooj

• ###### 2. Re: Difference between subcategory WRT Sales

Hello Mahfooj,

Thanks a lot for the reply.

My question is: for example For India my highest subcategory might be Phones But if i change Country filter My subcategory May vary right?

In that case can i keep Relative to as FIRST instead of selecting subcategory as Phones?

Regards,

Koushik.

• ###### 3. Re: Difference between subcategory WRT Sales

Yeah in that case you can keep relative to as FIRST. You've mentioned in your question that you're comparing with Phone that's why I've suggested you to make it relative to Phone sub-category else I would suggest FIRST.

Mahfooj

• ###### 4. Re: Difference between subcategory WRT Sales

Hello Mahfooj,

Thank you and sorry for bothering you.

If i keep relative as FIRST then my first value is showing as 0. Is there any way to show the one doing the most in first value and others with the difference.??

It should get as below:

Regards,

Koushik.

• ###### 5. Re: Difference between subcategory WRT Sales

Hi Koushik,

You need to select Sub-Category as the Specific Dimension after selecting edit quick calculation.

Hope this helps!!

Thanks,

Dhruv

1 of 1 people found this helpful
• ###### 6. Re: Difference between subcategory WRT Sales

Yes, when i select specific dimension my First Value is showing zero? My highest Value should show me as it is and respective category should show me the difference taking highest value.

• ###### 7. Re: Difference between subcategory WRT Sales

Hi Koushik,

I am not sure if it is possible to retain the original value for a specific cell and show the calculated values for rest of cells in column(after adding calculation). I think you can re-drag the Sales column to show the original values side by side to the difference column.

Hope this helps!!

Thanks,

Dhruv

• ###### 8. Re: Difference between subcategory WRT Sales

Hello Dhruv,

Thanks a lot for the reply. My requirement is for calculated field Phones should show the actual value  and rest of the categories should show the difference of highest Value

Regards,

Koushik.

• ###### 9. Re: Difference between subcategory WRT Sales

Hi Koushik,

In that case, there is only one alternative i.e. to use 2 sheets and superimpose the Top Sub-Category value sheet on the detailed Sub-Category sheet; like below. I have used version 10.1.1 to make these changes.

Hope this Helps!!

Thanks,

Dhruv

• ###### 10. Re: Difference between subcategory WRT Sales

Try this

Hope this is what you wanted!

Mahfooj

2 of 2 people found this helpful
• ###### 11. Re: Difference between subcategory WRT Sales

Eaxctly but the values are in Minus, The calculated value should be + sign?

• ###### 12. Re: Difference between subcategory WRT Sales

As per the above screenshot--> 6241-2931=3410..but its displaying as -3410.

• ###### 13. Re: Difference between subcategory WRT Sales

I got it,

Previously due to this showing -ve

IF INDEX()=1 THEN SUM([Sales]) ELSE

ZN(SUM([Sales (copy)])) - LOOKUP(ZN(SUM([Sales (copy)])), FIRST())

END

Use this calculation to get +ve

IF INDEX()=1 THEN SUM([Sales]) ELSE

LOOKUP(ZN(SUM([Sales (copy)])), FIRST()) - ZN(SUM([Sales (copy)]))

END

Hope this is what you wanted!

Mahfooj

1 of 1 people found this helpful
• ###### 14. Re: Difference between subcategory WRT Sales

Sirji...Excellent

Thanks a lot

1 2 Previous Next