Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DominicK
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

Hi @DominicK ,

You can update the formula as below:

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

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

12 REPLIES 12
az38
Community Champion
Community Champion

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

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!

az38
Community Champion
Community Champion

@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

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

 

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yiruan-msft 

 

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] )

Hi @DominicK ,

You can update the formula as below:

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

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yiruan-msft 

 

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

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yiruan-msft How do i upload the file?

Hi @DominicK ,

You can upload it to OneDrive for Business and share the link with me. 

Share OneDrive files and folders

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks Rena - I have messaged you the link

 

@v-yiruan-msft 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.