I just want to create a new calculated measure ("Quantity_Sales") based on the standard-measure "Quantity" in the Cube "Inventory".
In the new calculated measure i would like to see only the "Quantity" of "Item Ledger Entry Type" = "Sales".
How can i do it? What is the right expression?
Hello Andreas -
Have you seen the article on Creating a Calculated Measure?
Hello Harry,
yes, but in this articel is nothing about using simple mdx-expressions...
I think i need a simple "select"-command.
Andreas,
Jet Analytics builds on the Microsoft BI stack. In this case, your question is actually... 'how do i write an MDX Expression', and it might make sense to try to understand MDX.
For your use case, the MDX SUM() function is appropriate, and if you look at Microsoft's documentation, you'll see you need a Set Expression and the measure you ant to aggregate.
In your case, a suitable example might be:
SUM(
[Item ledger entry type].[Item Ledger Entry Type].[Sales]
, [Quantity]
)
Keep in mind that MDX and SQL have completely different syntax and structure! If you have further questions, please feel to reach out me at Jae@OnyxReporting.com
I'm assuming you're using the NAV project. It's going to look like this which specifies the intersection of the measure, dimension, dimension attribute, and dimension member.
([Measures].[Quantity], [Item Ledger Entry Type].[Item Ledger Entry Type].[Sales])
David Zebrowitz
Many thanks! Problem solved.
You're welcome Andreas. When it comes time for more advanced calcs, there are a lot of articles out there on MDX Calculated Members. Calculated Member is the proper term for SQL Analysis Services Multidimensional Cubes. The only trick is you need to only look at what's between the "WITH MEMBER ... AS" and the "SELECT"
For instance, the below MDX query example, you will only use the Bold part in a JDM Calculated Measure:
WITH MEMBER [Measures].[Special Discount] AS
[Measures].[Discount Amount] * 1.5
SELECT [Measures].[Special Discount] on COLUMNS
, NON EMPTY [Product].[Product].MEMBERS ON Rows
FROM [Adventure Works] WHERE [Product].[Category].[Bikes]
Here is some documentation from Microsoft:
I'm glad it helped!
David Zebrowitz