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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.