Forum Discussion
Calculation on Grouped Columns
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.
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 a lot,
I'm a beginner with PowerBI. I wasn't sure what you mean by "Selected Values", Let me share what I have done so far:
I created two measures to count how many "false" and how many "true" I have.
MeasureCountOverdueFalse = Calculate (count(Incidents2017[Overdue Status]),FILTER(Incidents2017,Incidents2017[Overdue Status]="false"))
MeasureCountOverdueTrue = Calculate (count(Incidents2017[Overdue Status]),FILTER(Incidents2017,Incidents2017[Overdue Status]="true"))
Then I created two measures to express these as percentages:
MeasureOverduePercentFalse = ([MeasureCountOverdueFalse]/([MeasureCountOverdueFalse]+[MeasureCountOverdueTrue])*100)
MeasureOverduePercentTrue = ([MeasureCountOverdueTrue]/([MeasureCountOverdueFalse]+[MeasureCountOverdueTrue])*100)
But I ended up with a 100% stacked bar chart, not exactly what I had in mind. Basically, I wanted the chart on the left but data labels as percentages of that particular bar.
What am I doing wrong?
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.