Gaspard Martin-Jouannaud Posted February 9 Share Posted February 9 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 More sharing options...
Gaia Paolini Posted February 12 Share Posted February 12 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. Link to comment Share on other sites More sharing options...
Gaspard Martin-Jouannaud Posted February 14 Author Share Posted February 14 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 More sharing options...
Gaia Paolini Posted February 16 Share Posted February 16 Maybe I misunderstood your question. Is it possible to post an anonymised sample dxp? Link to comment Share on other sites More sharing options...
Gaspard Martin-Jouannaud Posted February 26 Author Share Posted February 26 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 More sharing options...
Olivier Keugue Tadaa Posted February 26 Share Posted February 26 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 Link to comment Share on other sites More sharing options...
Olivier Keugue Tadaa Posted February 26 Share Posted February 26 with the data embedded population analysis.dxp Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now