Calculate % based off total, rather than selected

[Lenihan]

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#      Bad             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#      Bad             Jan

Random#      Good          Feb

Random#      Good           Jan

Random#      Bad             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. 

[vitinho]

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

[Lenihan]

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?

[vitinho]

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

[Lenihan]

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 ?

[vitinho]

%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. 😃

[Lenihan]

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

[Lenihan]

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

[Lenihan]

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

[vitinho]

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

[Lenihan]

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 ?

[Lenihan]

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.