none
MDX Query to get distribution percentage

    Question

  • I need to have an MDX query created as a calculated measure in an SSAS cube in the BIDS UI. I need it to calculate percent distribution of a field. See sample below. I need to get the highlighted columns by taking Aging Amount divided by the sum of the Aging Amount total for the office. I'd prefer if it didn't have to be limited by Division/office, as i may change the dimensions on the left to break down the data in other ways.


    Wednesday, July 17, 2013 8:34 PM

Answers

  • OK... I missed that, didn't notice you wanted to agg across rather than down.

    Does this work?

    CREATE MEMBER CURRENTCUBE.[Measures].[Distribution Percent]
    AS 
     IIF
        (
            (
              [Measures].[Aging Amount]
             ,Axis(0).Item(0).Item(0).Hierarchy.CurrentMember.Parent
            )
          = 0
         ,null
         ,
            [Measures].[Aging Amount]
          / 
            (
              [Measures].[Aging Amount] 
             ,Axis(0).Item(0).Item(0).Hierarchy.CurrentMember.Parent 
            )
          
        ) 
    , 
    FORMAT_STRING = "##0%;0%;\0%"
    


    My Blog: http://ditchiecubeblog.wordpress.com/

    • Proposed as answer by Duane Dicks Thursday, July 18, 2013 7:54 PM
    • Marked as answer by kayluhh Friday, July 19, 2013 9:54 PM
    Thursday, July 18, 2013 5:48 PM

All replies

  • Using axis should get you to create calculations irrespective of what dimensions are in play.

    Something like this:

    CREATE MEMBER CURRENTCUBE.[Measures].[Distribution Percentage]
    AS 
     IIF
        (
            (
              [Measures].[Ageing Amount]
             ,Axis(1).Item(0).Item(
              Axis(1).Item(0).Count - 1).Hierarchy.CurrentMember.Parent
            )
          = 0
         ,null
         ,
            [Measures].[Ageing Amount]
          / 
            (
              [Measures].[Ageing Amount] 
             ,Axis(1).Item(0).Item(
              Axis(1).Item(0).Count - 1).Hierarchy.CurrentMember.Parent
            )
          
        ) 
    , 
    FORMAT_STRING = "##0%;0%;\0%"
    

    This would give the participation of its parent essentially.

    I hope this helps.


    My Blog: http://ditchiecubeblog.wordpress.com/

    Wednesday, July 17, 2013 9:08 PM
  • I added that into the script command in BIDS and I just get a #VALUE! error?
    Wednesday, July 17, 2013 9:25 PM
  • It may be because I used the wrong measure names - spelt different to yours.

    Does this work? :

    CREATE MEMBER CURRENTCUBE.[Measures].[Distribution Percent]
    AS 
     IIF
        (
            (
              [Measures].[Aging Amount]
             ,Axis(1).Item(0).Item(
              Axis(1).Item(0).Count - 1).Hierarchy.CurrentMember.Parent
            )
          = 0
         ,null
         ,
            [Measures].[Aging Amount]
          / 
            (
              [Measures].[Aging Amount] 
             ,Axis(1).Item(0).Item(
              Axis(1).Item(0).Count - 1).Hierarchy.CurrentMember.Parent
            )
          
        ) 
    , 
    FORMAT_STRING = "##0%;0%;\0%"

    Aging....not Ageing

    My Blog: http://ditchiecubeblog.wordpress.com/

    Wednesday, July 17, 2013 9:50 PM
  • I had changed those. No difference. If i remove the office and division, it gives me a blank. If i try to add office back, it gives me the #VALUE!
    Wednesday, July 17, 2013 10:10 PM
  • Are you using excel to pivot?

    Or is there some way to get what the true error is when you get that #Value.

    If you run the query in SSMS and you hover over the #Value with the mouse it should show what the error is.

    I have simulated your query on my side and the solution that I offered does work.

    so it probably just needs a minor tweak.


    My Blog: http://ditchiecubeblog.wordpress.com/

    Thursday, July 18, 2013 6:39 AM
  • Oh, I missed one of the name changes for Aging. Woops! Thank you. 

    The only issue is that the distribution is going down the column. i need it to go across the rows. so it gets the distribution % spread between aging buckets by office. I'd expect to see 100%'s in the grand total on the right. Does that make sense? I'm thinking it's probably as simple as changing the axis 0 and 1, but i am not familiar with MDX at all. Help?

    EDIT: i swapped 0's and 1's on the axis. results are below. grand totals should show 100%on the right though


    another update...

    When i try to do this in Excel Pivot table, it just gives me blank values for Distribution Percentage. The goal is to use this in Excel and PerformancePoint Services for SharePoint.

    • Edited by kayluhh Thursday, July 18, 2013 1:54 PM excel problem
    Thursday, July 18, 2013 1:24 PM
  • OK... I missed that, didn't notice you wanted to agg across rather than down.

    Does this work?

    CREATE MEMBER CURRENTCUBE.[Measures].[Distribution Percent]
    AS 
     IIF
        (
            (
              [Measures].[Aging Amount]
             ,Axis(0).Item(0).Item(0).Hierarchy.CurrentMember.Parent
            )
          = 0
         ,null
         ,
            [Measures].[Aging Amount]
          / 
            (
              [Measures].[Aging Amount] 
             ,Axis(0).Item(0).Item(0).Hierarchy.CurrentMember.Parent 
            )
          
        ) 
    , 
    FORMAT_STRING = "##0%;0%;\0%"
    


    My Blog: http://ditchiecubeblog.wordpress.com/

    • Proposed as answer by Duane Dicks Thursday, July 18, 2013 7:54 PM
    • Marked as answer by kayluhh Friday, July 19, 2013 9:54 PM
    Thursday, July 18, 2013 5:48 PM