Divide Measure by dimension

Question

Trying Measure/Dimension - but keep getting errs - the latest err is "The currentmember function expects a hierarchy expression for the 1 argument. A member expression was used".
Below is the query. I am trying to divide amount by month number, like Feb is 2 then 100/2, 150 for Mar then 150/3 and so on.
with member [Measures].[MonNums] as [Time].[Calender].[MonthNum].Currentmember.Uniquename
select ([Measures].[Amount]/[Measures].[MonNums]) on columns,
[Time].[Calendar].[Year].&[2022] on rows from [CubeName].

Thank you,

Answer 1

Hi @K P ,

Please try with below code:

with member [Measures].[MonNums] as INT( [Time].[MonthNum].Currentmember.Uniquename )  
select ([Measures].[Amount]/[Measures].[MonNums]) on columns,  
 [Time].[Calender].[MonthNum].memberson rows from [CubeName].  

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Getting an error "Query (3,1) The members function expects a hierarchy expression for the argument. A member expression was used. & Query (1, 37) The following system error occurred: Type mismatch"

wrote exact query you are mentioning @ZoeHui-MSFT .

Thank you
K

Answer 3

Hi @K P ,

Please check below:

with member [Measures].[MonNums] as  
[Date].[Month of Year].currentmember.MEMBERVALUE  
member [Measures].[valuebymonth] as  
Divide([Measures].[Sales Amount],[Measures].[MonNums])  
select {[Measures].[Sales Amount],  
[Measures].[MonNums],  
[Measures].[valuebymonth] } on columns,  
[Date].[Calendar].[Month].members on rows  
from [Adventure Works NEW]  
Where [Date].[Calendar Year].&[2011]  

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.