Jump to content

Change statically calculated column depending on binning

Recommended Posts

I have a bar chart that displays the number of items in a specific category. It's based on a table with only items from this category.

However instead of the number of items I would like to display the percentage of the total items that the items from this category make up. I don't have this info in my table so I calculate it in another table by creating a column with the expression Count([ITEMS]) over (Concatenate(Year([DATE]),Month([DATE]))) -> (the concatenate is actually another column). I add this column to my original table and calculate the proportion : 1 / [TOTAL]. Then I sum it in the vis : Sum([PROPORTION]).

This works but since the proportion is calculated statically, it does not change when the binning of the date changes, making the proportions incorrect. Is there a simple way of achieving this that I'm overlooking ? If not is there a way to detect the change in binning and recalculate the [TOTAL] column ?

Link to comment
Share on other sites

Hello Gaia,

I don't really understand your suggestion. If you're talking about the Count([ITEMS]) over (Concatenate(Year([DATE]),Month([DATE]))) expression, this one comes from another table so I would need to change the source and limit the data in the visual to items from the desired category. Even then I would have the same problem because I would still have the statically calculated Concatenate(Year([DATE]),Month([DATE])) in the expression which would not change depending on the binning.

Thank you for your response

Link to comment
Share on other sites

  • 2 weeks later...

I'm sorry, I don't have the time to post a dxp, however I've found a solution to my problem even though it's not the cleanest. I put some buttons to do the binning below the visualization and use the doc prop value from those buttons in both the binning expression of the date axis and in my calculated column. I'm still open to suggestions for a better solution though.

Link to comment
Share on other sites

Hello Gaspard , could you try solution in your Value axis custom expression ?

Count([ITEMS])  / (Count([ITEMS]) over  (All([Axis.X])))

It will calculate the percentage of the bar over all the values on your X-Axis whatever is the selected hierarchy level. 
Hope this helps. I've attached an example.


population analysis.dxp

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...