Showing the Sales Hierarchy in a single bar chart(See the description)

[Anonymous]

I have a requirement where I need to show the Sales of Organization,Category,Product in a single bar chart. Expression to be used - Sum(Sales)

Something like this

Here Category and Product are given as filters to the user, so it depends on user selection.

I tried this using Value List.

Dimension - ValueList('Organization',Category,Product)

Expression - SUM(Sales)

Now the User Selects Fruits from Category and Apple from Product. The chart I got was like

I know there is just a tweak that needs to be done. Would be helpful if someone can help me on this.

[tamilarasu]

Hi koushik,

Why you are using single quotes for Organization dimension. If it is a dimension, directly mention the name i.e

ValueList(Organization,Category,Product)

[Anonymous]

Organization is not a dimension. SUM(Sales) gives the sales of Organization. At the same time, I need to show the Sales for Category and Product as well.

[tamilarasu]

If Organization is not a dimension means, what is the purpose of Organization here. Value list function used to created a temporary dimension. I.e field values for a dimension.  Could you clarify why you use value list.

[Anonymous]

I need to show the Bar as Organization so that it can be compared with Category and Product. That is the reason why I used Value List to show a Dummy dimension as Organization.

If you look at my question, I mentioned that Sales needs to be compared across these three.

Let me explain this clearly

Chart 1: SUM(Sales) if given as an expression in a bar chart gives me Organization level Sales

Chart 2: If I add Category as Dimension and give SUM(Sales) as an expression - gives me Sales across all Category

Chart 3: If I add Product as Dimension and give SUM(Sales) as an expression - gives me Sales across all Product.

What I need is - Instead of these individual charts, it has to be done in a single chart so that when User selects Category and Product, the end result is a Bar chart showing sales across Organization,that particular Category and that particular Product

[effinty2112]

Hi,

I don't think synthetic dimensions are the way to go here.

If I understand you correctly the sales figure for Organization is total sales regardless of selections in the Category and Product fields so use the expression

Sales(Organization) = Sum(TOTAL Sales)

Sales figure for Category should disregard selections in Product but recognise selections in the Category field

Sales(Category) = Sum(TOTAL <Category> Sales)

And finally

Sales(Product) = Sum(Sales)

[Anonymous · Accepted solution]

Ok. Found the solution.

Chart with No Dimension but 3 expression. Disable Legends

1st expression - SUM({<Category=,Product=>}Sales), Label - Organization

2nd expression - SUM({<Product=>}Sales), Label - =Category

3rd expression - SUM(Sales), Label - =Product

[settu_periasamy]

Hi,

Try this..

Dimension : valuelist('Organization','Category','Product')

Expression : Pick (Match (valuelist('Organization','Category','Product'),'Organization','Category','Product'),your org.Expression 1,category expression2,Product expression3)

[tamilarasu]

Glad you found the solution.

[Anonymous]

Yes Andrew. I did exactly as that. Thanks for your answer. In fact, I had already attached the solution to the thread.