I apologize in advance for what I’m sure is a newbie question. I’m quite familiar with BI practices, but am only a few weeks deep into the Dundas methodology.
We’re trying to compare the output of 2 cubes (specifically some sums) and output a percentage. I’ll skip over all the business-specific jargon and go back to everyone’s favourite sample data: produce.
Table “Produce” has 3 columns: ID, Name, Category.
Sample Data:
ID Name Category
1 Banana Fruit
2 Apple Fruit
3 Cucumber Vegetable
4 Celery Vegetable
5 Orange Fruit
The Big Boss now would like to know what percentage of all produce are Fruits.
So far, I’ve created 2 cubes: one outputs all produce and there other further filters the output of the first to return only fruit. I can easily get the totals for both in their own metric sets. The wall I’m hitting is that I can’t figure out how to do a mathematical comparison between the two. I can’t add the first cube to the second as that generates our beloved “same source” error, same with adding both cubes to the same metric set. Thus, I have nowhere where I can type “return count($fruit$)/count($all$)” or whatever the syntax would be.
Obviously I can solve this by doing the math directly in SQL, but I’ve been working really hard to avoid SQL whenever possible as it means that few people other than myself will be able to maintain the reports.
Any and all ideas are very much appreciated in advance!