How to do math in Custom Group element ?

[mstrpup]

Hi all,

I am new to MicroStrategy and new to the group.

I am creating an Income Statement report that has a percentage as one of the rows and need to know how (if) this can be done in a Custom Group. An example would be:

Element 1: Sales = Filter on GL Account (Account # = 4000 – 4999)
Element 2: Cost of Sales = Filter on GL Account (Account # = 5000 – 5999)

I need Element 3 (Gross Margin %) to be ((Sales – Cost of Sales) / Sales * 100)

So if Sales is $100,000 and Cost of Sales is $60,000, then the Gross Margin % would be 40% ((100,000 - 60,000)/100,000 *100)


Does anyone know if this can be done and how?

Thanks in advance for any help!

TK

 

[entaroadun]

I would implement this differently.

I assume your income statement has a series of measures (metrics) going down vertically on the left hand side.

I would create separate metrics for each row. Create a Sales metric with the G/L filter built in. Create a Cost of Sales metric with the filter built in. Create a Gross Margin % metric using the other two metrics. You don't need to do the multiply by 100; just format your Gross Margin % metric as a percentage.

Pull all three metrics into your report and pivot your block of metrics so that they go up and down vertically.

 

[mstrpup]

Thanks for the quick response!

I guess I didn't give enough information. I am using multiple metrics in my columns already - for Actual Amount, Budget Amount, Variance, etc. So I cannot use metrics on the rows as well - as I understand it you can only use metrics on rows OR columns, not both.

 

[entaroadun]

OK, custom groups can't do this. However, consolidations can.

Create a consolidation. Consolidations work differently; they don't take filters; they take attribute elements. In your consolidation, create three elements. In the Sales element, include the 4000-4999 G/L Account attribute elements. Ditto for Cost of Sales.

For your third element, use your first two elements and write the formula. I don't think you need to multiply by 100; just format the element as a percent.

 

[mstrpup]

Hmmmm, not the answer I wanted - consolidations seem much more cumbersome than groups, but I will try it this way. Thank you for the help!!

 

[reisw]

Sounds like the real issue is being able to format the report in a particular way. The only real way to do this is Report Services or use narrowcast and format it in Excel