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.
Hello all,
I have been pulling data via odata and been trying to calculate percentages within my query
The code is as follows:
&"aggregate("
&"$count as TotalCount,"
&"cast(LastResultOutcome eq 'Passed', Edm.Int32) with sum as PassedCount,"
&"cast(LastResultOutcome eq 'Failed', Edm.Int32) with sum as FailedCount,"
&"cast(LastResultOutcome eq 'None', Edm.Int32) with sum as NotRunCount,"
&"cast(LastResultOutcome ne 'None', Edm.Int32) with sum as RunCount"
&")"
&")"
&"/compute("
&"RunCount div TotalCount as RunPercentage,"
&"NotRunCount div TotalCount as NotRunPercentage,"
&"iif(TotalCount gt NotRunCount, PassedCount div RunCount,0) as PassedPercentage,"
&"iif(TotalCount gt NotRunCount, FailedCount div RunCount,0) as FailedPercentage"
&")"
&"&$orderby=RunPercentage desc", null, [Implementation="2.0"])
however, these are my results:
TotalCount PassedCount FailedCount NotRunCount RunCount RunPercentage NotRunPercentage PassedPercentage FailedPercentage
63 21 0 42 21 0 0 1 0
was wondering why the percentage fields are returning either 0 or 1. Hope someone can help me out here
Solved! Go to Solution.
Hi @ak105
The results are correct because both dividend and divisor are Integers. When you use div or /, it returns the data type of the argument with the higher precedence. For more information, see Data Type Precedence (Transact-SQL). If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated. Reference: / (Division) (Transact-SQL).
To have the percentage fields return decimal numbers, you can convert the dividends or divisors to float or decimal data types.
Data type conversion (Database Engine)
CAST and CONVERT (Transact-SQL)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Thank you! This helps!
Hi @ak105
The results are correct because both dividend and divisor are Integers. When you use div or /, it returns the data type of the argument with the higher precedence. For more information, see Data Type Precedence (Transact-SQL). If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated. Reference: / (Division) (Transact-SQL).
To have the percentage fields return decimal numbers, you can convert the dividends or divisors to float or decimal data types.
Data type conversion (Database Engine)
CAST and CONVERT (Transact-SQL)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
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 |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |