Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
Can you help me about a nested set analysis ?
I want to get the sum of invoices from a department, only for certain products.
To do that, I tried to use :
=Sum({$<DEPARTMENT={"Sum({$<PRODUCT_FAMILY={"PRODUCT_1","PRODUCT_2"}>}[AMOUNT_INVOICED])>'DEPARTMENT_NUMBER'"}>}[AMOUNT_INVOICED])
But it doesn't work.
Can you help me to solve this ?
Thanks lot
Perhaps this:
=Sum({$<DEPARTMENT={'DEPARTMENT_NUMBER'},PRODUCT_FAMILY={"PRODUCT_1","PRODUCT_2"}>}[AMOUNT_INVOICED])
Can you provide some sample data?
Try this
=Sum({$<DEPARTMENT={"=Sum({$<PRODUCT_FAMILY={'PRODUCT_1','PRODUCT_2'}>}[AMOUNT_INVOICED])>10000 "}>}[AMOUNT_INVOICED])
This will retrieve sum of all amount where sum of product1 and product2 > 10.000
10.000 can be a variable
=Sum({$<DEPARTMENT={"=Sum({$<PRODUCT_FAMILY={'PRODUCT_1','PRODUCT_2'}>}[AMOUNT_INVOICED])>$(=some_var)"}>}[AMOUNT_INVOICED])
Put an '=' sign in the inner sum() and single quotes for products, like :
Sum({$<DEPARTMENT={"=Sum({$<PRODUCT_FAMILY={'PRODUCT_1','PRODUCT_2'}>}[AMOUNT_INVOICED])> DEPARTMENT_NUMBER "}>}[AMOUNT_INVOICED])
Perhaps this:
=Sum({$<DEPARTMENT={'DEPARTMENT_NUMBER'},PRODUCT_FAMILY={"PRODUCT_1","PRODUCT_2"}>}[AMOUNT_INVOICED])
Hello!
I don't get what the DEPARTMENT_NUMBER have to do with the sum of AMOUNT_INVOICED. Why compare them?
Anyway, if you want the sum of invoices only for certain products (or families), and view it by department, you need to us DEPARTMENT as a dimension and this expression:
Sum({$<PRODUCT_FAMILY = {'PRODUCT_FAMILY_1', 'PRODUCT_FAMILY_2'}>} AMOUNT_INVOICED)
or
Sum({$<PRODUCT = {'PRODUCT_1', 'PRODUCT_2'}>} AMOUNT_INVOICED)
Notice one expression is on the level of the product families and the other on the level of the products themselves. Mixing them wouldn't work.
But if what you want is the total sum of invoices of all products of the departments that have sold certain products, the expression would be like:
=Sum({$<DEPARTMENT = {P({$<PRODUCT = 'PRODUCT_1', 'PRODUCT_2'}>} DEPARTMENT)}>} AMOUNT_INVOICED)
Adjust for PRODUCT_FAMILY as required.
Hope it helps.
Regards.
Try this
=Sum({$<PRODUCT_FAMILY={"PRODUCT_1","PRODUCT_2"}>}[AMOUNT_INVOICED])
I also believe that this expression is correct!!!
Thank you very much for all your replies !