Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Updated application with some changes in load script and a chart with simpler expressions is attached.
Is this what you were looking for?
Create a Straight table
Dimension: Product
Expression 1: Sum([Total Licenses])
Expression 2: Sum([Total Licenses])/Sum(TOTAL [Total Licenses])
The straight table isn't the issue. I have been able to do that with no issues.
The reason I'm not suing Sum(TOTAL expression) above is that there are some Product values I don't want to include that aren't included in my table example above.
I need to get this value in my load script so I can use the percent when I load other data. How might I do that?
One option is to use a flag with values 1 or 0 for each product (if it is based on the product) depending on if you want to include or exclude a product and use
Sum(TOTAL (Flag *[Total Licenses]))
for getting the total.
If you give your complete requirements, you may get more useful answer from the forum.
I am attaching an example of what I'm trying to describe. Maybe this will help explain what I'm looking for.
Updated application with some changes in load script and a chart with simpler expressions is attached.
Is this what you were looking for?
flag the product in the script
License_By_Product:
LOAD [Product],
[Total Licenses],
[Customer Number],
if(Match(Product, 'Basic', 'Plus', 'Complete'), 1, 0) as Flag
FROM
Licenses.xlsx
(ooxml, embedded labels, table is Sheet1);
then use in your chart
expression =sum({$ <Flag={1}>} [Total Licenses])
Thanks!!!