6 Replies Latest reply on Oct 13, 2019 10:22 PM by Hari Ankem

# Creating a table which display the dimension values along with the calculated column in the column shelf.

I need to display a table as in the image where I display individual quarters and the calculated column QoQ which is calculated from the max(Qtr) and its previous quarter value.

I have calculated the QoQ. But I am unable to display the data in the format since Qtrs containing the Q1'18,Q1'19 etc is a dimension and QoQ is a measure.

Can someone guide me how I can achieve this?

The Table contains - Q1'18,Q1'19, Q2'18,Q2'19, QoQ.

I need the above mentioned Quarters along with QoQ in one single table and as separate columns. Any help is highly appreciated.

Attached is the image of the view that i am looking for.

• ###### 1. Re: Creating a table which display the dimension values along with the calculated column in the column shelf.

See if this helps: IF COUNTD(YEAR([Order Date])*100+DATEPART("quarter",[Order Date]))=1 THEN

SUM([Sales])

ELSE

SUM(

IF DATEPART("quarter",[Order Date])={MAX(DATEPART("quarter",[Order Date]))} THEN

IF YEAR([Order Date])={MAX(YEAR([Order Date]))} THEN

[Sales]

ELSE

-[Sales]

END

END)

END

• ###### 2. Re: Creating a table which display the dimension values along with the calculated column in the column shelf.

Thanks for the immediate reply. But I was looking for QoQ=(Q2'19-Q1'19)/Q1'19.

Can you please suggest how this can be achieved?

Sowmya

• ###### 3. Re: Creating a table which display the dimension values along with the calculated column in the column shelf.

Thanks for the immediate reply. But I was looking for QoQ=(Q2'19-Q1'19)/Q1'19.

Can you please suggest how this can be achieved?

Sowmya

• ###### 4. Re: Creating a table which display the dimension values along with the calculated column in the column shelf.

I have already shown how Q2-Q1 can be done above. Modify the formula accordingly to divide by Q1 as required.

• ###### 5. Re: Creating a table which display the dimension values along with the calculated column in the column shelf.

Since one single variable is used to calculate the QoQ, I am unable to divide the same by the previous Qtr value. Is there an alternate method to do the same cause in the future if I need to calculate YoY as well this approach will get complicated.

• ###### 6. Re: Creating a table which display the dimension values along with the calculated column in the column shelf.

Then you always have the opportunity to do your calculations as required and display the value on a new worksheet, and combine the sheets on a dashboard.