0

Dividing cost by number of months

Hi all,

I'm hoping somewhere here might be able to assist me, as I'm relatively new to Pyramid and am have issues with a particular calculation.

I am trying to set up a way to get a PMPM (per member per month) value based on the 'cost per member', and the number of months within the Paid Date period (in this example, it would be based on two months).

I can get the value I need if I merely divide the Cost Per Member by 2 (which I did with CPM/2), but I want this to be usable regardless of the number of months in the time period used.

For "Test1 PMPM(CPM/Mth)", I tried to do a count based on the current paid date member, but it appears to just be dividing by 1 (which makes sense as there is only one CURRENTMEMBER).  The properties are as follows:
( [Measures].[#~~6fced50e-17aa-470d-bbc0-79c890e0c8ae~~#] , [Episode].[Episode].CURRENTMEMBER ) /COUNT( ( [Paid Date].[Paid Date].CURRENTMEMBER ) )  


For Test2 PMPM(CPM/Mth), I tried to do a count it based on the sum of the paid date CURRENTMEMBER (thinking it'd register that there were 2 months within my customer member). It only appears to be working correctly for 'Test_Cerebrovascular Disease' though). The properties are as follows:
( [Measures].[#~~6fced50e-17aa-470d-bbc0-79c890e0c8ae~~#] ) / SUM(
 {  { [Paid Date].[Paid Date].CURRENTMEMBER }  }  ,  ( [Measures].DEFAULTMEMBER ) ) 

 

My Paid Date is using customer member ClaimsPaidJanFeb2017, which is an aggregate of January and February.  The properties are as follows:

AGGREGATE({
[Paid Date].[Paid Date].[Paid Month].&[2017-01-01T00:00:00],  [Paid Date].[Paid Date].[Paid Month].&[2017-02-01T00:00:00]
})   

 

Any help would be greatly appreciated!  Thank you in advance!

2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    1. For your custom aggregate member, ClaimsPaidJanFeb2017, instead of hard-coding it, I would  make this generic and create a parameter that allows multi selection.  Your code would look more like this.  Note that you will also have to select Multi-Mode:  Cumulative in your calculation designer in BI Office.
      1. AGGREGATE({
        [Paid Date].[Paid Date].[!@YourParameterName@!]
        
        })   
        
    2. For your calculated measure, you can then use the count on your multi-select parameter, instead of trying to use count on CURRENTMEMBER.  The could would look something like this (once again select  Multi-Mode:  Cumulative).
      1. [Measures].[Cost Per Member]
        /
        COUNT( STRTOSET( '{[Paid Date].[Paid Date].[!@YourParameterName@!]}' ) )        

    Here is the output from an example that I worked through with this on the AdventureWorks cube.  The top image is with two months selected, and the bottom image is with three months selected. As you can see, the amount is being divided by two and then by three.

     

    Reply Like
  • If I understand your requirements correctly, this can be accomplished easily without recourse to writing any MDX.

    1. Create a Parameter that contains the range of months you potentially want to select. Call this "Starting month". Default it to the first month  in the selected series.

    2. Duplicate the Parameter, rename it "Ending Month" and default it to whatever month you want after the first month.

    3. Selecting both parameters, right click and use Member Sets, Set Operators, Range. Call this set "Selected Periods". You now have a set of months that user can select based on the start and end month parameters.

    4. Click on the set and right click, Member Calculations, Aggregate functions, Aggregate to create a total for the set.

    5. Do the same again, but select Count to count how many months have been selected.

    6. Take the set aggregate and divide it by the set count to create an average per month.

    7. Layout your grid as you need it.

    Here is an example. the user can select the start and end month form the parameter, which drives the set, which drives the aggregate and count which drives the average. I've included the months selected set to display in the grid as well as the number of months selected, the aggregate and average per month for clarity.

     

    Reply Like 1
login to reply
Like Follow
  • Status Answered
  • 2 mths agoLast active
  • 2Replies
  • 116Views
  • 3 Following