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! that was easy :)
I'm trying to do that same with a stacked bar chart, but there I can't find something to do percentages "per bar" and only "percent of grand total".
Any ideas?
In this case, you're going to have to manually calculate (DAX) the measure. You'll need the sum of all Selected Values and then the %Distribution = Selected Value/Sum of all Selected Values. That translates to Selected Value (True or False) and Sum of all Selected Values (True AND False). In the case of Created Month 1 (217 True, 37 False), that means you'd have:
For True: 217/254 = 85.4%
For False 37/254 = 14.6%
You would enter the %Distribution as the value for the graph.
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.