cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper III

## Calculate % based off total, rather than selected

I should warn you first off - I'm new - only been using Power BI for Desktop for 2 days, so still trying to understand everything. I have target date though coming up that I need to build several reports and quickly.

This is a basic layout of data to help explain what I'm attempting to do.

Table1

ColumnA     Result          Month

Random#      Good          Feb

Random#      Good           Jan

Random#      Unknown    Jan

Random#      Good           Jan

Random#      Good          Jan

Random#     Unknown    Jan

Random#     Unknown    Feb

Random#     Unknown    Jan

Random#      Good         Jan

Random#      Good          Feb

Random#      Good           Jan

Random#      Good          Feb

Random#      Good           Jan

I want a line chart, that has 2 lines  - One for Good by Month, other for Bad by Month.  I've filtered out the Unknowns. The values is %GT Count of Random#.  I want the Percentage to be representative of all the rows (Good, Bad, Unknown), but - I only want to show Good and Bad on the line chart. Once I filter out Unknown, the percentages shift to be reflective as if there are the only options Good and Bad.

So - I want Good / Good + Bad + unknown  but it is calculating based on Good / Good + Bad

I saw several answers similar to this, but I can't seem to get them working - and with my limited knowledge so far, its a struggle. I have watched almost all of the videos on the Guided Learning.

11 REPLIES 11
Helper I

Hi!

Fisrt thing you can create a new measure like:
Total = COUNTROWS('Tabela2')

Then you can calculate the percentages
%Good = COUNTX(FILTER('Tabela2';
'Tabela2'[Result]="Good");
'Tabela2'[Result]) /  [Total]

Try this and see if works

Helper III

Hi vitinho,

Thank you for the quick reply

The percentage calculation (2nd part) - where would I enter that? Is that a new measure, or how would I get that formula on my visual?

also - tabela2 - was that table1 then, or was I supposed to create a new table?

Helper I

Table 2 is the same as your table 1, my bad.

The second step has to be a New Column, in my example named as %Good

Helper III

This is what I entered with my true table/column names.

%Cancelled = COUNTX(FILTER('Dec 2016 Closed Changes',
'Dec 2016 Closed Changes'[Closure code]= "Cancelled"),
'Dec 2016 Closed Changes'[Closure code] / [TotalCloseCode])

Your formula had semi-colons, but when I went to enter that, it underlined them in red that there were issues - so i switched then to commas. Hopefully that didn't change anything.

I get this error

"Cannot convert value 'Success' of type Text to type Number."

"Success" is one of the possible closure code values along with Cancelled. But - not sure why its erroring on that value since I'm searching for Cancelled ?

Helper I

%Cancelled = COUNTX(FILTER('Dec 2016 Closed Changes',
'Dec 2016 Closed Changes'[Closure code]= "Cancelled"),
'Dec 2016 Closed Changes'[Closure code]) / [TotalCloseCode]

The semicollons are because I'm using the portuguese version...
ANd I tink now it should work, you put the last parenthesis after the division (/) and then power bi were trying to calculate [Closure Code]/measure. That's were the error came from. 😃

Helper III

I'm getting there.

For some reason, it is multiplying, and not dividing.

%Cancelled = COUNTX(FILTER('Dec 2016 Closed Changes',
'Dec 2016 Closed Changes'[Closure code]= "Cancelled"),
'Dec 2016 Closed Changes'[Closure code]) / [TotalCloseCode]

It is coming out with a value that is the product of the total Cancelled and the TotalCloseCode.

edit: Even if I use just part of the formula

TotalCancelled = COUNTX(FILTER('Dec 2016 Closed Changes',
'Dec 2016 Closed Changes'[Closure code]= "Cancelled"),
'Dec 2016 Closed Changes'[Closure code])

The value is coming out with the Total # of cancelled x the total number of rows.      I'm not sure what I'm doing wrong. I'm determining this by checking that row to add the card visual

Helper III

Ok, I figured it out - it was a new measure I needed. The new column would just populate the value all the way down all the rows, which is why it was as if multiplying. When I created the new measure for the %Cancelled, I was able to obtain the value needed.

Thank you for all the help vitinho

Helper III

I was just thinking,

So - i renamed the dataset so that it is just "Closed Changes" as I will be appending new months to the table.

%Cancelled = COUNTX(FILTER('Closed Changes',
'Closed Changes'[Closure code]= "Cancelled"),
'Closed Changes'[Closure code]) / [TotalCloseCode] * 100

But - if I append a new month of data on, that will mess up the results won't it? Using this formula above, I won't be able to provide the monthly values, will I? I'm assuming since the TotalCloseCode (which is TotalCloseCode = COUNTROWS('Closed Changes')  when I add the new month on, this %cancelled will be based on two months of data. I won't be able to filter out each month of data.

Am I correct? If yes - I need to rethink how this is done, because I need to be able to obtain these values monthly

Helper I

I've tried here and you're a good to go with this model.
If your columns are like

Table1

ColumnA     Result          Month

Power Bi will automatically divide you data by month once the month is set as axis of your two line graphic

Helper III

Wouldn't this new measure be incorrect though?

TotalCloseCode = COUNTROWS('Closed Changes')

This would count the rows of all data rows that keep getting appended. But I would be calculating the percentage individually for each month. How can I get it that it will only count the rows individually for each month, without having to add a new measure each month ?

Helper III

Can anyone confirm it is possible to calculate percentage of just those selected month over month, where new data will continue to be appended? If I use the Countrows option, it will count all the rows for all the months.  Unless there is another way to calculate the percentage so that it keeps the true percentage and doesn't recalculate % values after doing selections/filtering.

I don't want to have to add a new measure every month, else in a couple of years, there will be quite a few new measures.

Announcements

#### Power BI Monthly Update - November 2023

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

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors