Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Osalah
Regular Visitor

Calculation on Grouped Columns

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:

ITTS_Incidents_-_Power_BI_Desktop_2017-11-27_11-15-55.pngNow 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.

2 ACCEPTED SOLUTIONS
tjd
Impactful Individual
Impactful Individual

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.

View solution in original post

tjd
Impactful Individual
Impactful Individual

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.

View solution in original post

6 REPLIES 6
tjd
Impactful Individual
Impactful Individual

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
Regular Visitor

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?

ITTS_Incidents_-_Power_BI_Desktop_2017-11-28_10-10-49.png

tjd
Impactful Individual
Impactful Individual

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
Regular Visitor

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?

 ITTS_Incidents_-_Power_BI_Desktop_2017-11-29_09-20-00.png

tjd
Impactful Individual
Impactful Individual

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
Regular Visitor

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

best regards

Osama S.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.