help with MDX query please
i need to calculated stock pcs for current member as sum of SAME region and only for distribution centers
for example
first row Object = 'WH_1'
Region = 'A'
i need to get sum of all ObjectType = 'Distibution Center'
for region 'A'
=12 pcs
sql example:
with cte_inv
as (
select a.ObjectID, b.ObjectType, b.Region, a.pcs
from FactInventory a
inner join DimObjects b on b.object = a.object
)
, cte_invByDC
as (
select Region, pcsRegion = sum(pcs)
from cte_inv
where ObjectType = 'Distibution Center'
group by Region
)
select i.ObjectID, i.ObjectType, i.Region
, i.pcs
, ir.pcsRegion
from cte_inv i
left join cte_invByDC ir on ir.Region = i.Region