% IN DECIMALS NOT GETTING CORRECTLY IN DAX POWER BI

[MA654654]

Hello, I am trying calculate the difference between Order Compliance and Target. I am using below DAX to find it however I am getting incorrect decimals sometime for eg my target is 85 and compliance is 83.5 ideally I should get -1.5 but I am getting -1.3. can't find where is the issue when I trying to break it the compliance Order ie No of Orders compliance and Non compliance counts are matching however when it comes to percentage it showing different values.

VAR _var = 'ORDER Table Measure'[Sales - ORDERCompliance] - 'ORDER Table Measure'[ORDER Target]

VAR _pct = DIVIDE('ORDER Table Measure'[Sales - ORDERCompliance], 'ORDER Table Measure'[ORDER Target]) - 1

VAR _rounded_pct = ROUND(_pct * 100, 1)

VAR _sign = IF(_var > 0, "+", "")

VAR _Sign1 = IF(_var > 0, "↗", "↘")

RETURN

_Sign1 & " " & _sign & FORMAT(_rounded_pct, "0") & "%"

To Find the No of Compliance Order I am using below 

Sales - ComplianceOrders % = " ComplianceOrders - " & FORMAT('Table Measure'[Sales - ComplianceORDERS],"#,0.0" & "%" & "")

[v-nuoc-msft]

Hi @MA654654 

Create a measure and try the following code:

Measure = 
VAR _var = SELECTEDVALUE('ORDER Table Measure'[Sales - ORDERCompliance]) - SELECTEDVALUE('ORDER Table Measure'[ORDER Target])

VAR _pct = DIVIDE(SELECTEDVALUE('ORDER Table Measure'[Sales - ORDERCompliance]), SELECTEDVALUE('ORDER Table Measure'[ORDER Target])) - 1

VAR _rounded_pct = ROUND(_pct * 100, 1)

VAR _sign = IF(_var > 0, "+", "")

VAR _Sign1 = IF(_var > 0, "↗", "↘")

RETURN 
_Sign1 & " " & _sign & FORMAT(_rounded_pct, "0") & "%"

Here I tested if _var was calculated correctly.

Here is the result.

If you still have problems, it is best to provide the pbix file and be careful to delete sensitive data.

Regards,

Nono Chen

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

[MA654654]

My Sales OrderCompliance and Target is a measure so I not able to use Selected Value in the query Below is my Sales - ComplaianceOrders % measure and Sales - ComplianceOrders % = " ComplianceOrders - " & FORMAT('Table Measure'[Sales - ComplianceORDERS],"#,0.0" & "%" & "")

[v-nuoc-msft · Accepted solution]

Hi @MA654654 

Allow me to pose a question here, what is your 'Table Measure' [Sales - ComplianceORDERS]?

I don't see anything in your description that relates to this.

If it is the name of the measure in your question.

Here are the results I got.

Sales - ComplianceORDERS = 
VAR _var = 'ORDER Table Measure'[measure Sales - ORDERCompliance] - 'ORDER Table Measure'[Measure ORDER Target]

VAR _pct = DIVIDE('ORDER Table Measure'[measure Sales - ORDERCompliance], 'ORDER Table Measure'[Measure ORDER Target]) - 1

VAR _rounded_pct = ROUND(_pct * 100, 1)

VAR _sign = IF(_var > 0, "+", "")

VAR _Sign1 = IF(_var > 0, "↗", "↘")

RETURN 
_Sign1 & " " & _sign & FORMAT(_rounded_pct, "0") & "%"

Sales - ComplianceOrders % = 
" ComplianceOrders - " & FORMAT('ORDER Table Measure'[Sales - ComplianceORDERS],"#,0.0" & "%" & "")

I hope you can help me explain this part if I have misunderstood.

Regards,

Nono Chen

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

[ExcelMonke]

Consider adding a decimal place to your Format Clause. E.g. FORMAT(_rounded_pct,"0.0%")