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')
)
)
Where MaxYear is my variable which i have created
What specifically is your question?
What do you want to do?
Try this out:
if(Year_Date = $(MaxYear),
if(Sum([Total Services Charges]) > 100000, Num(sum([Total Services Charges]/1000000),'$#,##0.00;($#,##0.00)') & ' M',
if(Sum([Total Services Charges]) > 1000, Num(sum([Total Services Charges]/1000),'$#,##0.00;($#,##0.00)') & ' K',
Num(sum([Total Services Charges]/1000),'$#,##0.00;($#,##0.00)') & ' K')
)
)
So what i am trying to achieve here is , I have historical data eg (2016 ,2017) But i want to display my visualization by latest year data . In order to achieve that i created a variable Max Year which store maximum year value . By comparing that variable with my field Year to date i want to do my calculations. , if i try writing it using Set analysis it will work . I need help to write my code using set analysis.
Set Analysis is for more efficient filtering - its not a replacement for an If statement such as your expression. Your If is a branching If rather than a filter. You could kludge something together using a set expression but is unlikely to perform better and it certainly won't be simpler or more intuitive than a properly structured nested if.
I put the Num() outside the Sum() statements. It does nothing inside the Sum().
If(Year_Date = MaxYear,
If(Sum([Total Services Charges]) > 100000,
Num(sum([Total Services Charges]) / 1000000) & ' M',
If(Sum([Total Services Charges]) > 1000,
Num(Sum([Total Services Charges]) / 1000) & ' K',
Num(Sum([Total Services Charges]))
)
)
)