Thursday, 21 July 2016

Sorting on Table Calc

How to Sort  Order Date quarters based on a table calculation. This puts the chronology of the dates out of order, but allows us to see which dates had the largest changes. Also Dynamically choose the department to sort the difference calculation on.



Note:-
It is not possible to sort on a table calculation from a dimension in the sorting options and the default sorting will not work.


Step 1:-

Create a parameter that includes all of the Category items. 


Step 2:-

Create calculation that returns the sales of a particular category




Step 3:-

Drag "Selected Sales LOD" on Rows Shelf, convert to Discrete, and place in front of Order Date on Rows Shelf. 


Step 4:-

Use quick table calculations to convert the LOD to a Difference calculation. 


Step 5:-

Swap the dimensions on the row shelf to sort by your  calculation. 




Deselect "Show Header". 


Right click on the created parameter, and "Show Parameter Control". 




Thanks Guru's,
Prazval.ks




DAX Time Intelligence Functions

   Thanks Guru's, Prazval.Ks