# Calculating multiple measures in one MDX

HI, when trying to get multiple calculations in one Measure, I cant seem to find the right MDX.
I have ;

Sum ( CASE WHEN [Vendor].CurrentMember.Name = ‘Obsidian Code’ OR
[Vendor].CurrentMember.Name = ‘Obsidian Data’ OR
[Vendor].CurrentMember.Name = ‘Obsidian Teams’ OR
[Vendor].CurrentMember.Name = ‘Obsidian Managed Services’ OR
[Vendor].CurrentMember.Name = ‘Obsidian Compute’
THEN
[Measures].[Profit by Expected Close date] *.33
CASE WHEN [Vendor].CurrentMember.Name = ‘Atlassian’
THEN
[Measures].[Profit by Expected Close date] /2
ELSE
[Measures].[Profit by Expected Close date]
END)

So what I want is when it is all the Obsidian Vendors I want the numbers divided by .33% - if there is an additional vendor tied to any of those I then want those numbers further divided by half.

These are all based off another measure "profit by expected close date which is basically cost-sales.

I am not sure if I should be using a tuple, aggregate or a sum? Any help would be greatly appreciated.

Thanks

Hi @Dean1,

You are on the right track, using condition CASE WHEN THEN you can apply different rates for groups of vendors. To make this calculation work, you might want to implement a few improvements to the formula. Aggregate function Sum() has two import data to work correctly:

• set of items, in your case, Vendors;
• numeric value, in your case, measure “Profit by Expected Close date” with all calculation conditions.

The improved formula might look like this:

``````Sum(
--set of vendors
Descendants([Vendor].CurrentMember,[Vendor].[Vendor])
,
--numeric expression, calculation according to vendor name
CASE WHEN --vendor with applicable rate 0.33
[Vendor].CurrentMember.Name MATCHES
"Obsidian Code|Obsidian Data|Obsidian Teams|Obsidian Managed Services|Obsidian Compute"
THEN [Vendor].[Profit by Expected Close date] * 0.33
WHEN --vendors with applicable rate 0.5
[Vendor].CurrentMember.Name = "Atlassian"
THEN [Measures].[Profit by Expected Close date] / 2
ELSE --rest of vendors
[Measures].[Profit by Expected Close date]
END
``````

For more details on sets and how to build calculated measures and regular expressions to filter by name, please see the documentation:

Check out also training video on MDX ABC and how to work with the sets (important part for aggregate functions):

Best,
Zane / support@eazyBI.com

1 Like