Calculating a % of a subset

[dan_ossmann]

I have the total active customers by product. I have products organized into schools. I can calculate the percent each product represents of the total of all products regardless of school. I do this in a module with two line items, Active and Active %. The formula is Active / Active[SELECT:’Programs P2’.All Programs]. I would like to calculate the percent each product represents of the total of products in the school. I do not understand how to calculate the divisor, the sum of the products in only one school. The products are in a list called Programs P2. The Schools are in a list called Schools. Programs P2 has a list-formatted property called School.

[steve_taylor · Accepted solution]

Hi Dan, If I am understanding your requirement correctly, it sounds like you've laid all the foundations you need to do this and it is just a case of getting the calculation to reference the right things. You said that you have created the a list formatted property on the Programs P2 list called School. You can reference this in the formula to get it to look up the relevant parent (or school) value for each product. The formula in the new line item (e.g. School Active %) should read: Active / Active[LOOKUP: 'Programs P2'.School] I hope this makes sense. If you need more information, let me know. Steve

[dan_ossmann]

I do try this and get an error related to the dimensions in the source module. i.e. 'format of mapping used for lookup doesn'tmatch any dimension of the source." I get it that the souce module does not have the school dimension. the source data does not inherently have school. I was kind of hoping that I could get at this total using the property. So my data looks like this. I have only the one line item (Active). The only dimension in the module is Product. I thought I would insert a new line item with the schools property subset for SOBT on that line item alone (but it didn't). then i thought i would create a new module with Product and Schools dimensions but the formula still wants the source to have the dimension schools. how do i write a formula that looks at the property of the product and sum based on that property?

	Line item	Property
	Active	School
Product 1	42	SOBT
Product 2	96	SOE
Product 3	45	SOUS
Product 4	67	SOBT
Product 5	83	SOBT
Product 6	14	SOE

[nathan_rudman]

Often, it is useful to split your formula. Try & create a new line item "Schools" formatted with the formula 'Programs P2'.School Then do a lookup on this one.