It is very easy to group or slice the measures in the Pivot table by adding a attribute to the row or slicer. But, how do we create the same effect in the Calculated Column?

Here is the Sample Data

If we treat this table as a SQL table, we could use the following T-SQL query to find the total queued time for each queue per date.

SELECT [Date], [QueueName], SUM([QueuedTime]) AS [TotalQueuedTime]

`FROM CallDetail`

GROUP BY [Date], [QueueName]

In PowerPivot, we could use the EARLIER function to achieve the same effect. EARLIER is a very useful DAX function when you want to use a certain value as input to produce a calculation.

#### Syntax:

`EARLIER(<column>, <number>) `

#### Solution:

Step 1: Calculate the total of the QueuedTime by using **SUM(), **and then use **FILTER**() to obtain the subset of the table for the Date and the QueueName needed.

=CALCULATE(

` SUM(CallDetail[QueuedTime]), `

` FILTER(CallDetail, CallDetail[Date] = ?) `

&& CallDetail[QueueName]= ?

)

)

Step 2: In the question mark area, we need to plug in a value/expression to restrict the rows been returned.

If we set they to the static value as “2012-02-18” and “Queue A”, the Sum of QueuedTime will be 900 for all the rows which won’t be correct for the Queue not equal to “Queue A” or the Date not equal to “2012-02-18”.

**EARLIER**() as the input for the certain value (i.e. Date or Queue name) that the calculation will be based on it.

=CALCULATE(

` SUM(CallDetail[QueuedTime]), `

FILTER(CallDetail, CallDetail[Date] = EARLIER(CallDetail[Date])

&& CallDetail[QueueName]= EARLIER(CallDetail[QueueName])

)

)

#### Implementation Example:

Here is a scenario that I have encountered: Create a measure that contains the cumulated Queued Time only for the last queue.

To solve it, I add the following condition to the pervious formula based on the LastQueue flag

`=IF(CallDetail[LastQueue] = 1, `

CALCULATE(

` SUM(CallDetail[QueuedTime]), `

FILTER(CallDetail, CallDetail[Date] = EARLIER(CallDetail[Date])

&& CallDetail[CallID]=EARLIER(CallDetail[CallID])

)

)

,BLANK()

)

This way, only the last queue will have the total queued time in the column.

When summing up in the Pivot table, the total of QueueTimeHandled per day will now match the total of QueuedTime.

My colleague Javier Guillen has another nice blog entry to discuss how to use the EARLIER() in DAX measure. You may want to check it out.

Interesting read, thanks for sharing!

ReplyDeleteIn this group there is hardly anyone who can know about this section i detail.so they are opening this new function here as quickly as possible.So they may start their work soon.

ReplyDeleteGaming Computer Cases

So beautiful and helpful article. I think this post composed of with some learning and acquiring lot of things.

ReplyDeleteDesktop PCs

Thank you, this solved a similar problem of mine. Cheers!

ReplyDelete