Customizing using Pick()

    Customizing the straight /Pivot tables are pretty common tasks in Application development. And those including adding calculated and columns like  YTD at the end of the month dimension, and many other use cases can be sufficed using this little trick I learned recently from stalwar1

     

     

    As we all know how pick function works and to have a quick look here

     

     

    If we have a table where we need to show the months as Columns and a YTD value at the end, then we can create a calculated dimension like below:

    Step1: Create a table say Dim in line with values 1,2.

    Step2: Use pick (Dim, month,'YTD') as the dimension.

    Step3: With expression like Sum({<MonthNum = {"$(='<=' & max(MonthNum))"}, Month>}Amount)) we can achive the required functionality.

    Now the table changes dynamically with the month selected in the filter, and a YTD value at the end.

     

     

    Using the above example we can write custom expressions for the columns using pick(Dim, Dim1, Dim2) as dimension and pick(Dim,Expression1 ,Expression2) where expression1 is evaluated fo Dim1 and Expression2 will be evaluated for Dim2.

     

     

    We can also apply the same logic for the rows in the pivot table while calculating customized subtotals etc. Mainly while calculating the balance sheets.

     

    Attached is a very basic application depicting this usecase.