# question

## MDX how to get total sum for sub groups?

mdxresult.png (56.6 KiB)
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Likes 0 ·

·

base-query.jpg (294.4 KiB)

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Because of the fact that you have the Income Range dimension on your axis, you also have to include it in the calculated member. If you don't (like your example above), the aggregation will take place over the year and then be broken down by the other dimensions on the axis. Let me know if you need a more explanatory example...wanted to keep it brief, but I can show you some code if required.
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

So are you saying that I will not be able to have a subgroup total because of the income range on the axis? Examples are always welcome, especially when I have someone who can explain it to me! Regards Jon
0 Likes 0 ·
Hi Martin, Thank you for the elaborate explanation, and you even made screen shots! It seems so obvious now but I was just drawing a blank. My subtotal however needs further filtering, MEMBER Measures.AdvGroupTotal as SUM ( FILTER( EXISTING ([Dim IFA Details].[Parent Key].[Adviser Group].Members * [Dim Date].[Fiscal].[Fiscal Year].members ) , Measures.[Amount] > 100 ) , Measures.[Amount]) However my total does not have this clause. How do I work in this subgroup exclusion without breaking the total sum? Regards Jon

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Continuing with my example (just because it's easier to test)... If I wanted to exclude product lines with less than \$100,000 sales for the year in my calculated member, the syntax for the calculated member would look like this: with member [Measures].[Test] as ( sum ( {[Date].[Fiscal Year].&} * {[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year]} * {[Sales Channel].[Sales Channel].[Sales Channel]} * { filter ( {[Product].[Product Line].[Product Line]} , [Measures].[Sales Amount] > 100000 ) } , [Measures].[Sales Amount] ) ) Notice that I did not need to include any members from the fiscal date hierarchy in the filter clause. It is already part of the cross join and does not need to be added again. To test that this worked I included the product line as a field on my axis, and verified that product lines with less than \$100,000 were still shown but excluded from the yearly total. You could do the same by adding the Advisor Group attribute temporarily. Hope this helps.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This is what I came up with as well, but my results were not what I hoped for. MEMBER [Measures].[Test] AS SUM([Dim Date].[Fiscal Year].CurrentMember * [Dim Date].[Fiscal Quarter].[Fiscal Quarter] * [Dim Income Range].[Hierarchy].[Range ID] * { filter ( {[Dim IFA Details].[Parent Key].[Adviser Group]} , [Measures].[Amount] > 100 ) } , Measures.Amount ) This comes back with different values for each quarter and band. So I figured maybe it didnt like the levels, (The Dim IFA Details is a self referencing table, with Parent-Child hierarchy) and changed it to [Dim IFA Details].[Parent Key]. This worked but it did not appear to filter out the subgroups with less then 100.
0 Likes 0 ·
Can you post the actual and expected results? Seeing what your query returns may help to troubleshoot further.
0 Likes 0 ·

mdxresult.png (76.1 KiB)

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The grouping seems to be on the income range for all adviser groups. When i changed the filter to [Measures].[Amount] > 1000000 it put a null in the test column for up £10,000 as the AdvGroupTotal for that was 441088.82. However it still put the total 53086694.3 in the test column for the other ranges, which again is the total of all ranges.
0 Likes 0 ·
The calculation is grouping correctly. Remember that your calculated member is filtering on "Amount", and for lines 2-5 the "Amount" measure on each line is less than 100 (and therefore excluded from the calculation).
0 Likes 0 ·
![alt text] : /storage/temp/2297-mdxresult.png The result when filter is increased to which results in one of the ranges becoming null

mdxresult.png (71.2 KiB)

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The fact table is at adviser level, I am grouping on adviser group level by year and want to exclude those less then 100. Then sum that across all income ranges How can I achieve this with the query above?

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Forgive me, but I'm a little lost after your last comments. Do you need an "Adviser Group" to be filtered out (excluded from the calculation) if the yearly sum is less than the specified amount, or the quarterly sum?
0 Likes 0 ·
Yearly sum. So if the adviser group only generates 100 or less per year, I would like to exclude it from all totals. This will only affect band 1, up to 10000 as obviously the rest are generating more then 100 per year. Sorry for the confusion.
0 Likes 0 ·
Ok...if you need to base your filtered set on the yearly sum, it is probably best to create a calculated set. In the code below, I have created a filtered set of years and sales channels...only if the sales amount is greater than \$10 million. with set [Filtered Channels] as ( filter ( {[Sales Channel].[Sales Channel].[Sales Channel]} * {[Date].[Fiscal Year].currentmember} , [Measures].[Sales Amount] > 10000000 ) ) member [Measures].[Set Results] as ( settostr([Filtered Channels]) ) member [Measures].[Test] as ( sum ( {[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year]} * {[Filtered Channels]} , [Measures].[Sales Amount] ) ) select { [Measures].[Sales Amount] , [Measures].[Test] , [Measures].[Set Results] } on 0 , { {[Date].[Fiscal Year].[Fiscal Year]} * {[Date].[Fiscal Quarter of Year].members} * {[Sales Channel].[Sales Channel].members} } on 1 from [Adventure Works] where { {[Date].[Fiscal].[Fiscal Year].&} } ; I then use the filtered set in the calculated measure, to ensure that the correct grain is used. Also note that I've added a calculated measure ([Measures].[Set Results]). This measure is for testing purposes only. I use this method frequently to see what the results of a crossjoin or filter operation would be. The results are below. You can see that only the "Reseller" channel is included in the total, because its yearly sum is greater than \$10 million. ![alt text] : /storage/temp/2309-filtered-set.png

filtered-set.png (199.1 KiB)

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Huzzah, that worked! Thank you so much for your patience and persistence. Another trick to add to the knowledge base! Now that we, or rather you, have solved the problem, is what I am doing a good idea? Its not particularly quick, which leaves me wondering if I shouldnt be doing this. Takes about 8 secs.
0 Likes 0 ·
Here is the final query: WITH SET [Filtered Groups] AS ( Filter ( { [Dim IFA Details].[Parent Key].[Adviser Group] } * { [Dim Date].[Fiscal Year].CurrentMember }, [Measures].[Amount] > 100 ) ) MEMBER [Measures].[Annual] AS Sum ( [Filtered Groups], Measures.[Amount] ) MEMBER [Measures].[Firm Count] AS DistinctCount ( [Filtered Groups] ) MEMBER [Measures].[Avg Income Per Firm] AS [Measures].[Annual] / [Measures].[Firm Count] MEMBER [Measures].[Test] AS Sum ( { [Dim Date].[Fiscal Quarter].[Fiscal Quarter] } * { [Dim Income Range].[Hierarchy].[Range ID] } * { [Filtered Groups] }, [Measures].[Amount] ) MEMBER [Measures].[Quarter] as [Measures].[Amount] SELECT { [Measures].[Quarter], [Measures].[Annual], [Measures].[Firm Count], [Measures].[Avg Income Per Firm], [Measures].[Test] } ON COLUMNS, [Dim Date].[Fiscal Year].[Fiscal Year].AllMembers * [Dim Date].[Fiscal Quarter].[Fiscal Quarter].AllMembers * Except ( [Dim Income Range].[Hierarchy].[Range ID].Members, [Dim Income Range].[Hierarchy].[All].UnknownMember.UnknownMember ) ON ROWS FROM [Income and Emails Cube] WHERE [Dim Date].[Fiscal].[Fiscal Year].&[FY 13/14]
0 Likes 0 ·
Cool...glad it worked. To answer your question, it really depends on the use-case for this. If it is for something like an SSRS report, it sometimes makes more sense to split the queries up for the sake of performance. If it seems to be a more general requirement, you should consider adding this filtered set into your cube's structure itself. Or the entire calculated measure...or both. If it's in the cube, at least your aggregations can relieve some of the heavy lifting being done on the fly now.
0 Likes 0 ·
Hi Martin, Can you elaborate how I can add this measure into the cube? What is the best way to keep a running annual total for income bands that ignore amounts below the threshold? My current report is too slow as I allow users to click bands to display other charts. Regards Jon
0 Likes 0 ·
You can collapse the 2 into one, and add it as you would any other calculated member (calculations tab in SSDT). It is common to create calculated members in the cube for annual running totals, and I would recommend doing that. Doing it on-the-fly will consume too many resources for large data sets.
0 Likes 0 · 