Change statically calculated column depending on binning

[Gaspard Martin-Jouannaud]

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 ?

[Gaia Paolini]

instead of creating a calculated column, have you tried using that formula as a custom expression for the Value  Axis (height of the bars)?

If you right-click where it asks you to choose a column, you can enter custom expressions.

[Gaspard Martin-Jouannaud]

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

[Gaia Paolini]

Maybe I misunderstood your question. Is it possible to post an anonymised sample dxp?

[Gaspard Martin-Jouannaud]

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.

[Olivier Keugue Tadaa]

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.

Thanks

population analysis.dxp

[Olivier Keugue Tadaa]

with the data embedded

population analysis.dxp