Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @DominicK ,
You can update the formula as below:
% Complete =
DIVIDE ( [Vehicles Missing Returns], COUNT ( [Vehicles] ) ) * 100
Best Regards
Rena
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!
VALUE() function converts a text string that represents a number to a number. https://docs.microsoft.com/en-us/dax/value-function-dax
I am trying to add the % Complete column to the right using something like the formula below.
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
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
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
Hi @DominicK ,
You can update the formula as below:
% Complete =
DIVIDE ( [Vehicles Missing Returns], COUNT ( [Vehicles] ) ) * 100
Best Regards
Rena
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 Name, Vehicles 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
Hi @DominicK ,
You can upload it to OneDrive for Business and share the link with me.
Share OneDrive files and folders
Best Regards
Rena
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |