Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
= if(Year_Date = MaxYear,
if(Sum([Total Services Charges]) > 100000, sum((Num([Total Services Charges]/1000000,'$#,##0.00;($#,##0.00)'))) & ' M',
if(Sum([Total Services Charges]) > 1000, sum(Num([Total Services Charges]/1000,'$#,##0.00;($#,##0.00)')) & ' K',
sum(Num([Total Services Charges]/1000,'$#,##0.00;($#,##0.00)')) & ' K')
)
)
Looks like you want to format the number to M/K based on its value, I don't think you can simplify it using set analysis as you have to check each aggregated Sum value to decide if you want to write M or K after that.
One approach could be to create island table of units M,K,Unit and allow user to select a unit user would like to see their numbers in , the other field in the same island table is the value in M, K and 1, which will be used to divide the sum expression. This will avoid the if statements but all the numbers will appear in single unit.