Forum Discussion
Calculation on Grouped Columns
- 8 years ago
Select the down arrow for your Value in the visual (SLA count?) and then select "Show as % of row total". That will convert the visual to a % of False and True for each CreatedMonth.
- 8 years ago
First, you're making this too complicated. You only need three measures:
RowCount = Number of rows in the Incidents2017 table (assuming they each have either a True or False Overdue Status. This value will get filtered by whatever categorical value you have in a column (Overdue Status for you and will result in two measures -one True and one False count).
Total Sum of RowCount = Calculate (RowCount, ALLSELECTED) This value is not filtered by a categorical vaule (ALLSELECTED) and results in the sum of RowCount (for False) and RowCount (for True).
%Distribution = Divide(RowCount, Total Sum of RowCount, 0) This value becomes your percentage distribution.
Then set your graph up as
Axis: CreatedMonth
Value: %Distribution (will filter based on what you put in legend)
Legend: Overdue Status (True/False will result automatically if that's all that's in the Overdue Status column
Your graph will default to a 100% stacked bar as that's what you're asking it to do - show me the % distribution of overdue status by each month created. The % True and % False always sums to 100% for each month.
For what you're trying to do, I would show the % as a tooltip and use RowCount as the Value. Then the graph will show correctly based on the count sum and % of each bar portion will be shown in the tooltip when you hover over it.
Hope this helps.
First, you're making this too complicated. You only need three measures:
RowCount = Number of rows in the Incidents2017 table (assuming they each have either a True or False Overdue Status. This value will get filtered by whatever categorical value you have in a column (Overdue Status for you and will result in two measures -one True and one False count).
Total Sum of RowCount = Calculate (RowCount, ALLSELECTED) This value is not filtered by a categorical vaule (ALLSELECTED) and results in the sum of RowCount (for False) and RowCount (for True).
%Distribution = Divide(RowCount, Total Sum of RowCount, 0) This value becomes your percentage distribution.
Then set your graph up as
Axis: CreatedMonth
Value: %Distribution (will filter based on what you put in legend)
Legend: Overdue Status (True/False will result automatically if that's all that's in the Overdue Status column
Your graph will default to a 100% stacked bar as that's what you're asking it to do - show me the % distribution of overdue status by each month created. The % True and % False always sums to 100% for each month.
For what you're trying to do, I would show the % as a tooltip and use RowCount as the Value. Then the graph will show correctly based on the count sum and % of each bar portion will be shown in the tooltip when you hover over it.
Hope this helps.
Thanks! That looks good, I can't try it until next week but I'm sure that will work.
best regards
Osama S.