Calculation on Grouped Columns

[Osalah]

Hi,

I've grouped my data according to months of record creation date (I have a field "CreatedMonth").

Then for each month I count the SLA Status (false and True)

It looks like this:

Now I would like for each row to show a percentage calculation of the count in "False" over the row total.

Appreciate help in accomplishing this.

thanks

Osama S.

[tjd · Accepted solution]

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.

[tjd · Accepted solution]

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.

[Osalah]

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?

[tjd]

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.

[Osalah]

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?

 

[tjd · Accepted solution]

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.

[Osalah]

Thanks! That looks good, I can't try it until next week but I'm sure that will work.

best regards

Osama S.