Forum Discussion

Lenihan's avatar
Lenihan
Helper III
9 years ago

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 rep...
Need Help
Lenihan's avatar
Lenihan
Helper III
9 years ago

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's avatar
vitinho
Helper I
9 years ago

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's avatar
    Lenihan
    Helper III
    9 years ago

    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's avatar
      vitinho
      Helper I
      9 years ago

      %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's avatar
        Lenihan
        Helper III
        9 years ago

        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