Forum Discussion

DominicK's avatar
DominicK
Helper I
6 years ago
Solved

Percent Complete between 2 columns - Underlying data is Text

https://community.powerbi.com/t5/Desktop/Percent-between-2-columns/td-p/410062   The idea is the same as the oringal thread however the underlying data is text and this is represented in PBI as a c...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi DominicK ,

    You can update the formula as below:

    % Complete =
DIVIDE ( [Vehicles Missing Returns], COUNT ( [Vehicles] ) ) * 100

    Best Regards

    Rena

DominicK's avatar
DominicK
Helper I
6 years ago

I am trying to add the % Complete column to the right using something like the formula below.

 

% Complete =
DIVIDE ( [Vehicles] , [Vehicles Missing Returns] )
 

The underlying data is:

 

171 Vehicles (rows of text) are in Section A, of those vehicles, 28 are missing values the % Complete column value I want to see would be 16.37%

 

I am showing Vehicles column as a "count" this is where the issue is 

 
 
 
 
 
 

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi DominicK ,

According to the error message, the data type of [Vehicles] or [Vehicles Missing Returns] is Text type... If I understand correctly, you set the field "Vehicles" with aggregation "Count" to apply on the visual. How about [Vehicles Missing Returns]? That is a field or measure? If it is measure, could you please provide the related formula? We will need to check if its returned value is Numeric type... If you can make sure the value of [Vehicles Missing Returns] is numeric value, please try to update the formula of measure "% Complete" as below:

% Complete =
DIVIDE ( COUNT ( [Vehicles] ), [Vehicles Missing Returns] )

If the above one is still not working or can't get correct value, please provide some sample data(exclude sensitive data) and explain the operations you have done.

Best Regards

Rena

  • DominicK's avatar
    DominicK
    Helper I
    6 years ago

    Hi Anonymous 

     

    We are almost there! thank you!

     

    I had to change the formula slightly to the below, now the last step is to multiply the value by 100.

    would this take place in the DAX formula?

     

    The top row should read 24%

    40/166*100=24

     

     

    % Complete =
    DIVIDE ( [Vehicles Missing Returns], COUNT ( [Vehicles] )
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi DominicK ,

      You can update the formula as below:

      % Complete =
DIVIDE ( [Vehicles Missing Returns], COUNT ( [Vehicles] ) ) * 100

      Best Regards

      Rena

      • DominicK's avatar
        DominicK
        Helper I
        6 years ago

        Anonymous 

         

        Thanks this works now, I do have some of the percentages that are calculating not completely accurate.

         

        Can I send you the data and explain the measures I have created so you can look through and advise?

         

        Thanks,

        Dom