asked
MDX how to get total sum for sub groups?
I have the following mdx query: with 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]) MEMBER Measures.[AdvGrpCount] as distinctCOUNT(Existing([Dim IFA Details].[Parent Key].[Adviser Group].Members * [Dim Date].[Fiscal].[Fiscal Year].members)) member Measures.AvgIncomePerFirm as Measures.AdvGroupTotal/ Measures.AdvGrpCount SELECT { [Measures].[Amount] , Measures.AdvGroupTotal, Measures.[AdvGrpCount], Measures.AvgIncomePerFirm} 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] ![alt text][1] [1]: /storage/temp/2277-mdxresult.png How can I the sum the AdvGroupTotals and AdvGrpCounts by Fiscal Year? I tried SUM ( FILTER( EXISTING ( [Dim Date].[Fiscal].[Fiscal Year].&[FY 13/14] ) , Measures.[Amount] > 100 ) , Measures.[Amount]) But even after dropping the [Dim IFA Details].[Parent Key].[Adviser Group].Members it still returned the same result as the existing calculated measure. My understanding was the existing command would turn off the current context allowing me to group by Fiscal year, but this is not what I am getting. What am I missing? Jon
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
No, not saying that at all. The way I usually like to explain it, is that the calculated member needs to be "aware" of the other dimension members in your query you want to aggregate over. Take a look at the following example from Adventure Works: ![Base Query][1] As you can see, the total sales for FY 2013 (across all sales channels) is about $24 million. Let's create a calculated measure to display the total sales for the year and across quarters/sales channels. We will first create it similar to what you've done (but taking some of your additional stuff out to make it easier to follow), and see what the results are: with member [Measures].[Test] as ( sum ( {[Date].[Fiscal Year].&[2013]} , [Measures].[Sales Amount] ) ) select { [Measures].[Sales Amount] , [Measures].[Test] } 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].&[2013]} } ; If you execute the above and as you've experienced too, the calculated measure contains the same values as Sales Amount (and therefore incorrect). As I've eluded to before, the reason for this is that the calculated member is not "aware" of the other dimension members used in your query. So the query engine is doing exactly what we've asked it to do...adding up the sales amount for the year, but then breaking it down by quarter and sales channel as we've indicated on axis 1...resulting in the same values. To fix this, we need to add the quarter and sales channel dimension members to the calculated member: ![Total Yearly Sales - Fix][2] Now you can see that the aggregation is performed across all quarters and sales channels, and we get the total sales for FY 2013 on each line. To make this query more dynamic (and work even if we include multiple years), I'd also recommend that you replace `{[Date].[Fiscal].[Fiscal Year].&[2013]}` with `{[Date].[Fiscal Year].currentmember}` Apologies for the long-winded explanation, but hope it helps. [1]:
https://ask.sqlservercentral.com/storage/attachments/2290-base-query.jpg [2]:
https://ask.sqlservercentral.com/storage/attachments/2292-total-yearly-sales-fix.jpg
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.
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
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].&[2013]} * {[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.
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.
Can you post the actual and expected results? Seeing what your query returns may help to troubleshoot further.
This is the query I have run: with 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]) MEMBER Measures.[AdvGrpCount] as distinctCOUNT( FILTER( Existing([Dim IFA Details].[Parent Key].[Adviser Group].Members * [Dim Date].[Fiscal].[Fiscal Year].members), Measures.[Amount] > 100 )) 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] } , [Measures].[Amount] > 100 ) } , Measures.Amount ) SELECT { [Measures].[Amount] , Measures.AdvGroupTotal, Measures.[AdvGrpCount], Measures.Test } ON COLUMNS, Filter( [Dim IFA Details].[Parent Key].[Adviser Group].members * [Dim Date].[Fiscal Year].[Fiscal Year].members * [Dim Date].[Fiscal Quarter].[Fiscal Quarter].members * Except( [Dim Income Range].[Hierarchy].[Range ID].members , [Dim Income Range].[Hierarchy].[All].UNKNOWNMEMBER.UNKNOWNMEMBER ) , not isempty( Measures.AdvGroupTotal)) on rows FROM [Income and Emails Cube] where [Dim Date].[Fiscal].[Fiscal Year].&[FY 13/14] ![alt text][1] [1]: /storage/temp/2295-mdxresult.png As you can see in the row 2- 5, the total is 287.22, but the test measure has come up with null. So it appears its not grouping correctly. That said the total without the axis breakdown by Adviser Group is still higher the its parts. When I change the filter set to [Dim IFA Details].[Parent Key].[Adviser Group] instead of just Parent Key (Adviser Group is the level) the totals are all different, and if I try it to the axis, its still running after 20 mins.
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.
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).
![alt text][1] [1]: /storage/temp/2297-mdxresult.png The result when filter is increased to which results in one of the ranges becoming null
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?
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?
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].&[2013]} } ; 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][1] [1]: /storage/temp/2309-filtered-set.png
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.
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]
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.
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
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.
16 People are following this question.
