PowerBi Desktop: Number Formatting in calculated Measures

Question

I want to achive below&nbsp;Expected number format in PowerBI calculated measure.Calculated Measure InputExpected Output359704359.7042716027.16015031.503999999999911Experts pls help on this. Thanks.Below Calculated Measure for your reference:Volume =&nbsp; &nbsp; IF((CALCULATE( &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; DISTINCTCOUNT('Table'[ID]),&nbsp; &nbsp; &nbsp; &nbsp; 'Table'[Worked] =&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECTEDVALUE('Table'[Worked]) = "Worked"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || SELECTEDVALUE('Table'[Worked items]) = "Worked",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Worked",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Un-Worked"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ))&gt;=1000,CALCULATE( &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; DISTINCTCOUNT('Table'[ID]),&nbsp; &nbsp; &nbsp; &nbsp; 'Table'[Worked] =&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECTEDVALUE('Table'[Worked]) = "Worked"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || SELECTEDVALUE('Table'[Worked items]) = "Worked",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Worked",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Un-Worked"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; )/1000, CALCULATE( &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; DISTINCTCOUNT('Table'[ID]),&nbsp; &nbsp; &nbsp; &nbsp; 'Table'[Worked] =&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECTEDVALUE('Table'[Worked]) = "Worked"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || SELECTEDVALUE('Table'[Worked items]) = "Worked",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Worked",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Un-Worked"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ))

rajendraongole1 · Answer

Hi&nbsp;Anonymous&nbsp;- Please try the below updated measure for converting numbers larger than 1,000 to thousands (K) format while leaving smaller numbers unchanged.
&nbsp;

Formatted Volume =
VAR CountValue =
&nbsp; &nbsp; CALCULATE(
&nbsp; &nbsp; &nbsp; &nbsp; DISTINCTCOUNT('Inpu'[ID]),
&nbsp; &nbsp; &nbsp; &nbsp; 'Inpu'[Worked] =
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECTEDVALUE('Inpu'[Worked]) = "Worked"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || SELECTEDVALUE('Inpu'[Worked items]) = "Worked",
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Worked",
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Un-Worked"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )
&nbsp; &nbsp; )
RETURN
IF(
&nbsp; &nbsp; CountValue &gt;= 1000,
&nbsp; &nbsp; FORMAT(CountValue / 1000, "#,###.000"), // Format numbers above 1000 with decimal places
&nbsp; &nbsp; FORMAT(CountValue, "#,###") &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;// Keep numbers below 1000 as they are
)

&nbsp;
&nbsp;
Hope this helps.
&nbsp;

Forum Discussion

PowerBi Desktop: Number Formatting in calculated Measures

1 Reply

Related content

PowerBi Desktop Currency format

Formatting Visual Calculations

Conditional formatting on calculated item in calculation group

conditional Formatting in Calculation Item

cant export to power bi desktop format

Calculated Measure Input	Expected Output
359704	359.704
27160	27.160
1503	1.503
999	999
99	99
1	1