I'm trying to create an expression that will retrieve values outside of the user's selection, and at a certain level. My fact table is stacked with multiple fact tables, an example of the data model below:
Tablename | Country | State | Sales | Target |
---|---|---|---|---|
Sales | US | California | 100 | null |
Sales | US | California | 200 | null |
Sales | US | New York | 200 | null |
Target | US | null | null | 500 |
Sales | CA | Toronto | 100 | null |
Sales | CA | Toronto | 200 | null |
Sales | CA | Vancouver | 200 | null |
Target | CA | null | null | 500 |
Sales are at a state/city level, whereas targets are at Country level. The users will select 1 state/city and put it against the country's target to see its contribution. For California, it will be 300 / 500 = 60%.
However since the users will filter by state/city, any target values will return 0.
I'm assuming p() needs to be used somewhere (since the relationship of sales and target are by the country value and not by association) but how do I achieve this via set analysis?
Also, what is the proper term used here where the Sales and Target rows have a relationship due to a common column value?
Thanks!
I was able to get this working with set analysis, the Aggr()
function, and the total
keyword. This expression works for me:
=Sum(Sales) / Sum(total <[Country]> Aggr(Sum({1} [Target]), [Country]))
The denominator in that expression broken out:
Sum(total <[Country]> Aggr(Sum({1} [Target]), [Country]))
^ ^ ^ ^
3. 4. 2. 1.
[Target]
field. We want to ignore users' selections for this part of the expression so we use the set identifier {1}
.Sum()
function inside an Aggr()
function so that we can aggregate or group the Sum()
on the [Country]
field.Aggr()
gave us a summed target value for each country, we now want to Sum()
those values but give the total
amount across all dimensions.total
to ignore, in this case we chose the [Country]
field so that we can see summed values rolled up to Country but not State/City.