[Solved] total function for a specific member

[Newb]

Hi all,

I'm not sure if the title makes sense. I am trying to create this amount2 data item in my query:
period  | category | amount1 | amount 2
Dec-21 | A           | 100        | <If category = A, then 1 which is 100%>
Jan-22 | A           | 200        | <If category = A, then 1 which is 100%>
Dec-21 | B           | 30         | <If category <> A, then amount1 of category B / amount1 of category A for the same period, which is 30/100>
Jan-22 | B           | 40          | <If category <> A, then amount1 of category B / amount1 of category A for the same period, which is 40/200>

I tried:
CASE WHEN category = A
THEN (1)
ELSE (amount1 / aggregate([amount1] for 'A', [period])
END

but the aggregate function didn't work as required, any suggestion please, like what functions can be used to acquired amount1 for A so that it can be used to calculate with amount1 for B

Thank you.

[MFGF]

Hi all,

I'm not sure if the title makes sense. I am trying to create this amount2 data item in my query:
period  | category | amount1 | amount 2
Dec-21 | A           | 100        | <If category = A, then 1 which is 100%>
Jan-22 | A           | 200        | <If category = A, then 1 which is 100%>
Dec-21 | B           | 30         | <If category <> A, then amount1 of category B / amount1 of category A for the same period, which is 30/100>
Jan-22 | B           | 40          | <If category <> A, then amount1 of category B / amount1 of category A for the same period, which is 40/200>

I tried:
CASE WHEN category = A
THEN (1)
ELSE (amount1 / aggregate([amount1] for 'A', [period])
END

but the aggregate function didn't work as required, any suggestion please, like what functions can be used to acquired amount1 for A so that it can be used to calculate with amount1 for B

Thank you.

Hi,
First question - is this a relational model or a dimensional model? The topic subject refers to "a specific member", and members are dimensional, but the expression you are using looks more like a relational expression? Also, is this a list or a crosstab?

I'll proceed under the assumption it's a dimensional model. If I'm understanding, you want the [amount1] measure for the [Category B] member divided by the [amount1] measure for the [Category A] member for the current member of the Period dimension? In a dimensional model, the context will be the period in the current crosstab row, so you don't need to worry about the period.

tuple(currentMember([Your cube].[Your Category Dimension].[Your Category Hierarchy]),[amount1]) / tuple([your category A member], [amount1])

For this to work, Period and Category need to be used as rows in a crosstab, Amount1 is a column in the crosstab, and the expression is a query calculation also used as a column in the crosstab. You don't need a case statement.

Cheers!

MF.

[Newb]

Hi MF,

My bad! It is actually a relational model, which means tuple functions cannot be used correct?

Thank you

[MFGF]

Hi MF,

My bad! It is actually a relational model, which means tuple functions cannot be used correct?

Thank you

In a relational model, you would need to isolate the total Amount values for Category A for each Period in a separate query, then join this to your main query based on Period. Otherwise you won't be able to get hold of the Category A amount values on the Category B rows.

So add a new query containing the Period and Amount1 items, and add a detail filter to the query for [category] = 'A'
The above query will return the total Amount1 values for each period for Category A

Then add a second new query containing the Period, Category and Amount1 items

Then join these two queries together (based on the Period item) into the query your list is based on. Add into this new query the Period, Category and Amount1 items from the second query, and add the Amount1 item from the first query (rename it to Category A Amount1)

Finally, go back to your list, add in the Period, Category and Amount1 items from your new query, and create your calculation as

CASE WHEN [category] = 'A'
THEN (1)
ELSE ([amount1] / [category A amount1])
END

Cheers!

MF.

[Newb]

Thanks MF,

Tried the join finally and it works!

Best Regards.