Number formatting issue in Pivot table-unable to use format (K,M,B)

[D_K_Singh]

Hello Genius,

I am trying to change the format of my measure in to (K,M,B) in pivot table. I have tried so many formula which i have used previously for Chart but it is not working while using in pivot table.

my measure formula is given below.

=sum(if([Opportunity Type (A / B /C/D/Others)]='A' and [Annual Spend (in Mn)]<=150000,[Annual Spend (in Mn)]))

Please suggest correct formula to convert into (K,M,B)

[BrunPierre]

Hi @D_K_Singh, Having replaced the Sum(If(...)) with set analysis, try the below.

Dual(Num(
Sum({$<[Opportunity Type (A / B /C/D/Others)]={'A'}, [Annual Spend (in Mn)]={"<=150000"}>} [Annual Spend (in Mn)])
/Pow(10,3*Floor(Log10(
Sum({$<[Opportunity Type (A / B /C/D/Others)]={'A'}, [Annual Spend (in Mn)]={"<=150000"}>} [Annual Spend (in Mn)])
)/3)),'#,###.#'&Pick(Floor(Log10(
Sum({$<[Opportunity Type (A / B /C/D/Others)]={'A'}, [Annual Spend (in Mn)]={"<=150000"}>} [Annual Spend (in Mn)])
)/3),'K','M','B')),
Sum({$<[Opportunity Type (A / B /C/D/Others)]={'A'}, [Annual Spend (in Mn)]={"<=150000"}>} [Annual Spend (in Mn)])
)

[D_K_Singh]

Hi Brun, This did not work, 

so the actual code which I have written.

Dual(Num(
Sum({$<[Opportunity Type (New Sourcing Request / Existing Contract renewal /Off Contract Buying/ Admin or Support Activity/Others)]={‘Existing Contract Renewal’}, [[Annual Spend in GBP])]={"<=150000"}>} [[Annual Spend in GBP])])
/Pow(10,3*Floor(Log10(
Sum({$<[Opportunity Type (New Sourcing Request / Existing Contract renewal /Off Contract Buying/ Admin or Support Activity/Others)]={‘Existing Contract Renewal’}, [[Annual Spend in GBP])]={"<=150000"}>} [[Annual Spend in GBP])])
)/3)),'#,###.#'&Pick(Floor(Log10(
Sum({$<[Opportunity Type (New Sourcing Request / Existing Contract renewal /Off Contract Buying/ Admin or Support Activity/Others)]={‘Existing Contract Renewal’}, [[Annual Spend in GBP])]={"<=150000"}>} [[Annual Spend in GBP])])
)/3),'K','M','B')),
Sum({$<[Opportunity Type (New Sourcing Request / Existing Contract renewal /Off Contract Buying/ Admin or Support Activity/Others)]={‘Existing Contract Renewal’}, [[Annual Spend in GBP])]={"<=150000"}>} [[Annual Spend in GBP])])
)

I have attached error as well for your reference.

[BrunPierre]

1. Use quotes for string values, not apostrophes.

={'Existing Contract Renewal'}

2.  The measure field has an unmatched Bracket  [[Annual Spend in GBP])] , and the syntax allows only a pair i.e. [Annual Spend in GBP]

[D_K_Singh]

I removed the (), still dint work, So what could be solution for this? Any suggestion please.

[BrunPierre]

Is there yet another syntax error or is the result not what you expected? Share it.