# Aggregating from a previous field within a load script

**Sophia Carmien**May 2, 2014 5:53 PM

Here's the first few lines of the table I am pulling my data from:

Product | Total Licenses | Customer Number |
---|---|---|

Basic | 1 | 2379 |

Complete | 30 | 4568 |

Basic | 123 | 8524 |

Plus | 7 | 5557 |

Plus | 44 | 4535 |

Basic | 1 | 7896 |

Complete | 1 | 7565 |

Basic | 8 | 8966 |

I would like to calculate what percent of total licenses are comprised of each product. For example, if I have 3,000 licenses for the product "Basic" and 10,000 licenses for all three products, then Basic would be 30% of consumer products. I need to use that 30% in other calculations.

I'm using the following to make a chart in my dashboard for expression "Percent Consumer". It works.

*sum({<Product = {'Basic'}>/<Product = {'Plus'}>/<Product = {'Complete'}>}[Total Licenses])/*

*(sum(total {<Product = {'Basic'}>}[Total Licenses])+*

*sum(total {<Product = {'Plus'}>}[Total Licenses])+*

*sum(total {<Product = {'Complete'}>}[Total Licenses]))*

What I'd really like to do is do the same kind of thing in my load script so I can use this value on other data I'm loading. I am trying the following:

*if(Product='Basic',*

* lookup([Total Licenses],Product,'Basic','License_By_Product')/*

* (lookup([Total Licenses],Product,'Basic','License_By_Product')+*

* lookup([Total Licenses],Product,'Plus','License_By_Product')+*

* lookup([Total Licenses],Product,'Complete','License_By_Product')),*

* if(Product='Plus',*

* lookup([Total Licenses],Product,'Plus','License_By_Product')/*

* (lookup([Total Licenses],Product,'Basic','License_By_Product')+*

* lookup([Total Licenses],Product,'Plus','License_By_Product')+*

* lookup([Total Licenses],Product,'Complete','License_By_Product')),*

* if(Product='Complete',*

* lookup([Total Licenses],Product,'Complete','License_By_Product')/*

* (lookup([Total Licenses],Product,'Basic','License_By_Product')+*

* lookup([Total Licenses],Product,'Plus','License_By_Product')+*

* lookup([Total Licenses],Product,'Complete','License_By_Product'))*

* ,0))) as [Percent Consumer]*

It's outputting a number, but it's the wrong number. I believe the reason for this is that "lookup" is only looking at one line in the table above. What I need is a way to sum up all the lookup values for a product. I tried using "sum" or "aggr" in conjunction with "lookup", but either that doesn't work or I'm doing it wrong.

Any advice?