Calculating multiple measures in one MDX

Questions & Answers
1

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

2

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