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

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

    • Lenihan's avatar
      Lenihan
      Helper III
      9 years ago

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

        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