1

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

enter image description here

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
Antonio
  • 51
  • 6

0 Answers0