cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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 count

Example would be: Water Target (underlying data is a COUNT of text values relating to each LOC code)

 

The DAX code posted looks like it would work but when I use that, the error I recieve is, "Can't divide Text Value"

 

Thanks!

Dom

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: haRe: Percent Complete between 2 columns - Underlying data is Text

Hi @DominicK ,

You can update the formula as below:

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

Best Regards

Rena

View solution in original post

12 REPLIES 12
Highlighted
Super User IV
Super User IV

Re: Percent Complete between 2 columns - Underlying data is Text

Hi @DominicK 

it means you have a text value for DIVIDE() argument

check data type for your columns 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Highlighted
Helper I
Helper I

Re: Percent Complete between 2 columns - Underlying data is Text

Hi @az38 

 

The underlying data is text but in PBI i am using the column as a COUNT.

 

Is there a way i can divide this count, or maybe create a new column to represent the text values as numbers?

 

Thanks!

Highlighted
Super User IV
Super User IV

Re: Percent Complete between 2 columns - Underlying data is Text

@DominicK 

VALUE() function converts a text string that represents a number to a number.  https://docs.microsoft.com/en-us/dax/value-function-dax 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Highlighted
Helper I
Helper I

Re: Percent Complete between 2 columns - Underlying data is Text

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 

 
 
 
 
 
 

 

DominicK_0-1593613931587.png

 

Highlighted
Community Support
Community Support

Re: Percent Complete between 2 columns - Underlying data is Text

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

Highlighted
Helper I
Helper I

haRe: Percent Complete between 2 columns - Underlying data is Text

Hi @yingyinr 

 

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

 

NEW.JPG

 

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

Re: haRe: Percent Complete between 2 columns - Underlying data is Text

Hi @DominicK ,

You can update the formula as below:

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

Best Regards

Rena

View solution in original post

Highlighted
Helper I
Helper I

Re: haRe: Percent Complete between 2 columns - Underlying data is Text

@yingyinr 

 

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

Highlighted
Community Support
Community Support

Re: haRe: Percent Complete between 2 columns - Underlying data is Text

Hi @DominicK ,

Please provide some source data in table "Raw" (include the field Vehicle NameVehicles and Cumulative Returns - YTD (03/2020) and so on) and the correct value of % Complete, then we can provide you the proper formula.

Best Regards

Rena

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors