Grand total issue in excel

Hi All,

I need to have a customized grand total in excel. Lets say I have Sales Quantity and Order quantity as two measures and I have city attribute. I need to get the grand total of Order quantity wherever I have a sales quantity. If there is no sales quantity and there is order quantity the grand total of Order quantity should exclude the numbers. Also, I have many cities and in excel we apply filters on rows to select only 5 cities. So my grand total should not show for all cities but it should show only for the selected cities.

Please let me know if you need additional details.

Regards,

Ram

July 29th, 2015 1:21pm

Hi Ramakrishnan,

According to your description, you want to calculate the order quantity where the sales quantity is not empty. Right?

In Analysis Services, we can have one measure show different fact data based on other measure. However, it's not supported have the condition applied on aggregation level only. In other words, for getting the "80", we can only filter those members where the sales quantity is empty, or make the order quantity rows return 0 when the sales quantity is empty. Because the Total row is generated automatically base on the aggregation usage we defined in measure.

For your requirement, I suggest you show the total separately using some calculated member like:

SUM(FILTER([City].[City].members,[Measures].[Sales Quantity]<0),[Measures].[Order Quantity])

Regards,

Free Windows Admin Tool Kit Click here and download it now
July 30th, 2015 12:57am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
City Sales Quantity Order Quantity
C1 100 10
C2 200 20
C3   30
C4   40
C5 300 50
Total   80