2 field (columns) combination Matrix Limits

[MyAtHome]

Hi ,
using excel 2010,
I want to use PowerPivot (not VBA, or SQL), if that is possible, to solve the following problem:
My model contains two tables.
First, I have a 2 field (columns) combination Matrix table of limits for a maximum investment.
For example:

[TABLE="width: 585"]
<tbody>[TR]
[TD="align: left"]Rating[/TD]
[TD="align: left"]Term[/TD]
[TD="align: left"]Max Aggregate Sum[/TD]
[TD="align: left"]Term|Rating[/TD]
[/TR]
[TR]
[TD="align: left"]AA[/TD]
[TD="align: left"]up to 1 Year[/TD]
[TD]1000[/TD]
[TD="align: left"]up to 1 Year|AA[/TD]
[/TR]
[TR]
[TD="align: left"]AA[/TD]
[TD="align: left"]1 year up to 3 Years[/TD]
[TD]900[/TD]
[TD="align: left"]1 year up to 3 Years|AA[/TD]
[/TR]
[TR]
[TD="align: left"]AA[/TD]
[TD="align: left"]3 Years up to 5 Years[/TD]
[TD]800[/TD]
[TD="align: left"]3 Years up to 5 Years|AA[/TD]
[/TR]
[TR]
[TD="align: left"]AA[/TD]
[TD="align: left"]more then 5 Years[/TD]
[TD]700[/TD]
[TD="align: left"]more then 5 Years|AA[/TD]
[/TR]
[TR]
[TD="align: left"]A+[/TD]
[TD="align: left"]up to 1 Year[/TD]
[TD]800[/TD]
[TD="align: left"]up to 1 Year|A+[/TD]
[/TR]
[TR]
[TD="align: left"]A+[/TD]
[TD="align: left"]1 year up to 3 Years[/TD]
[TD]800[/TD]
[TD="align: left"]1 year up to 3 Years|A+[/TD]
[/TR]
[TR]
[TD="align: left"]A+[/TD]
[TD="align: left"]3 Years up to 5 Years[/TD]
[TD]800[/TD]
[TD="align: left"]3 Years up to 5 Years|A+[/TD]
[/TR]
[TR]
[TD="align: left"]A+[/TD]
[TD="align: left"]more then 5 Years[/TD]
[TD]500[/TD]
[TD="align: left"]more then 5 Years|A+[/TD]
[/TR]
[TR]
[TD="align: left"]A[/TD]
[TD="align: left"]up to 1 Year[/TD]
[TD]600[/TD]
[TD="align: left"]up to 1 Year|A[/TD]
[/TR]
[TR]
[TD="align: left"]A[/TD]
[TD="align: left"]1 year up to 3 Years[/TD]
[TD]600[/TD]
[TD="align: left"]1 year up to 3 Years|A[/TD]
[/TR]
[TR]
[TD="align: left"]A[/TD]
[TD="align: left"]3 Years up to 5 Years[/TD]
[TD]500[/TD]
[TD="align: left"]3 Years up to 5 Years|A[/TD]
[/TR]
[TR]
[TD="align: left"]A[/TD]
[TD="align: left"]more then 5 Years[/TD]
[TD]300[/TD]
[TD="align: left"]more then 5 Years|A[/TD]
[/TR]
[TR]
[TD="align: left"]BB[/TD]
[TD="align: left"]up to 1 Year[/TD]
[TD]300[/TD]
[TD="align: left"]up to 1 Year|BB[/TD]
[/TR]
[TR]
[TD="align: left"]BB[/TD]
[TD="align: left"]1 year up to 3 Years[/TD]
[TD]200[/TD]
[TD="align: left"]1 year up to 3 Years|BB[/TD]
[/TR]
[TR]
[TD="align: left"]BB[/TD]
[TD="align: left"]3 Years up to 5 Years[/TD]
[TD]100[/TD]
[TD="align: left"]3 Years up to 5 Years|BB[/TD]
[/TR]
[TR]
[TD="align: left"]BB[/TD]
[TD="align: left"]more then 5 Years[/TD]
[TD]0[/TD]
[TD="align: left"]more then 5 Years|BB[/TD]
[/TR]
[TR]
[TD="align: left"]B+[/TD]
[TD="align: left"]up to 1 Year[/TD]
[TD]100[/TD]
[TD="align: left"]up to 1 Year|B+[/TD]
[/TR]
[TR]
[TD="align: left"]B+[/TD]
[TD="align: left"]1 year up to 3 Years[/TD]
[TD]50[/TD]
[TD="align: left"]1 year up to 3 Years|B+[/TD]
[/TR]
[TR]
[TD="align: left"]B+[/TD]
[TD="align: left"]3 Years up to 5 Years[/TD]
[TD]10[/TD]
[TD="align: left"]3 Years up to 5 Years|B+[/TD]
[/TR]
[TR]
[TD="align: left"]B+[/TD]
[TD="align: left"]more then 5 Years[/TD]
[TD]0[/TD]
[TD="align: left"]more then 5 Years|B+[/TD]
[/TR]
[TR]
[TD="align: left"]B[/TD]
[TD="align: left"]up to 1 Year[/TD]
[TD]0[/TD]
[TD="align: left"]up to 1 Year|B[/TD]
[/TR]
[TR]
[TD="align: left"]B[/TD]
[TD="align: left"]1 year up to 3 Years[/TD]
[TD]0[/TD]
[TD="align: left"]1 year up to 3 Years|B[/TD]
[/TR]
[TR]
[TD="align: left"]B[/TD]
[TD="align: left"]3 Years up to 5 Years[/TD]
[TD]0[/TD]
[TD="align: left"]3 Years up to 5 Years|B[/TD]
[/TR]
[TR]
[TD="align: left"]B[/TD]
[TD="align: left"]more then 5 Years[/TD]
[TD]0[/TD]
[TD="align: left"]more then 5 Years|B[/TD]
[/TR]
[TR]
[TD="align: left"]CC[/TD]
[TD="align: left"]up to 1 Year[/TD]
[TD]0[/TD]
[TD="align: left"]up to 1 Year|CC[/TD]
[/TR]
[TR]
[TD="align: left"]CC[/TD]
[TD="align: left"]1 year up to 3 Years[/TD]
[TD]0[/TD]
[TD="align: left"]1 year up to 3 Years|CC[/TD]
[/TR]
[TR]
[TD="align: left"]CC[/TD]
[TD="align: left"]3 Years up to 5 Years[/TD]
[TD]0[/TD]
[TD="align: left"]3 Years up to 5 Years|CC[/TD]
[/TR]
[TR]
[TD="align: left"]CC[/TD]
[TD="align: left"]more then 5 Years[/TD]
[TD]0[/TD]
[TD="align: left"]more then 5 Years|CC[/TD]
[/TR]
[TR]
[TD="align: left"]C+[/TD]
[TD="align: left"]up to 1 Year[/TD]
[TD]0[/TD]
[TD="align: left"]up to 1 Year|C+[/TD]
[/TR]
[TR]
[TD="align: left"]C+[/TD]
[TD="align: left"]1 year up to 3 Years[/TD]
[TD]0[/TD]
[TD="align: left"]1 year up to 3 Years|C+[/TD]
[/TR]
[TR]
[TD="align: left"]C+[/TD]
[TD="align: left"]3 Years up to 5 Years[/TD]
[TD]0[/TD]
[TD="align: left"]3 Years up to 5 Years|C+[/TD]
[/TR]
[TR]
[TD="align: left"]C+[/TD]
[TD="align: left"]more then 5 Years[/TD]
[TD]0[/TD]
[TD="align: left"]more then 5 Years|C+[/TD]
[/TR]
[TR]
[TD="align: left"]C[/TD]
[TD="align: left"]up to 1 Year[/TD]
[TD]0[/TD]
[TD="align: left"]up to 1 Year|C[/TD]
[/TR]
[TR]
[TD="align: left"]C[/TD]
[TD="align: left"]1 year up to 3 Years[/TD]
[TD]0[/TD]
[TD="align: left"]1 year up to 3 Years|C[/TD]
[/TR]
[TR]
[TD="align: left"]C[/TD]
[TD="align: left"]3 Years up to 5 Years[/TD]
[TD]0[/TD]
[TD="align: left"]3 Years up to 5 Years|C[/TD]
[/TR]
[TR]
[TD="align: left"]C[/TD]
[TD="align: left"]more then 5 Years[/TD]
[TD]0[/TD]
[TD="align: left"]more then 5 Years|C[/TD]
[/TR]
</tbody>[/TABLE]

Second, I have an investment table with a relationship to the first table, using the term|rating field. For example:
[TABLE="width: 599"]
<tbody>[TR]
[TD="align: left"]ID[/TD]
[TD="align: left"]Rating[/TD]
[TD="align: left"]Term[/TD]
[TD="align: left"]sum[/TD]
[TD="align: left"]Term|Rating[/TD]
[/TR]
[TR]
[TD]6195[/TD]
[TD]AA[/TD]
[TD="align: left"]1 year up to 3 Years[/TD]
[TD]75[/TD]
[TD="align: left"]1 year up to 3 Years|AA[/TD]
[/TR]
[TR]
[TD]7854[/TD]
[TD]A+[/TD]
[TD="align: left"]up to 1 Year[/TD]
[TD]33[/TD]
[TD="align: left"]up to 1 Year|A+[/TD]
[/TR]
[TR]
[TD]4597[/TD]
[TD]BB[/TD]
[TD="align: left"]more then 5 Years[/TD]
[TD]40[/TD]
[TD="align: left"]more then 5 Years|BB[/TD]
[/TR]
[TR]
[TD]6625[/TD]
[TD]CC[/TD]
[TD="align: left"]up to 1 Year[/TD]
[TD]100[/TD]
[TD="align: left"]up to 1 Year|CC[/TD]
[/TR]
[TR]
[TD]8362[/TD]
[TD]C+[/TD]
[TD="align: left"]1 year up to 3 Years[/TD]
[TD]50[/TD]
[TD="align: left"]1 year up to 3 Years|C+[/TD]
[/TR]
[TR]
[TD]2802[/TD]
[TD]BB[/TD]
[TD="align: left"]3 Years up to 5 Years[/TD]
[TD]60[/TD]
[TD="align: left"]3 Years up to 5 Years|BB[/TD]
[/TR]
[TR]
[TD]8068[/TD]
[TD]B+[/TD]
[TD="align: left"]3 Years up to 5 Years[/TD]
[TD]70[/TD]
[TD="align: left"]3 Years up to 5 Years|B+[/TD]
[/TR]
[TR]
[TD]4859[/TD]
[TD]AA[/TD]
[TD="align: left"]up to 1 Year[/TD]
[TD]551[/TD]
[TD="align: left"]up to 1 Year|AA[/TD]
[/TR]
[TR]
[TD]9417[/TD]
[TD]A+[/TD]
[TD="align: left"]more then 5 Years[/TD]
[TD]422[/TD]
[TD="align: left"]more then 5 Years|A+[/TD]
[/TR]
[TR]
[TD]2387[/TD]
[TD]BB[/TD]
[TD="align: left"]3 Years up to 5 Years[/TD]
[TD]602[/TD]
[TD="align: left"]3 Years up to 5 Years|BB[/TD]
[/TR]
[TR]
[TD]6766[/TD]
[TD]CC[/TD]
[TD="align: left"]more then 5 Years[/TD]
[TD]366[/TD]
[TD="align: left"]more then 5 Years|CC[/TD]
[/TR]
[TR]
[TD]5062[/TD]
[TD]C+[/TD]
[TD="align: left"]up to 1 Year[/TD]
[TD]731[/TD]
[TD="align: left"]up to 1 Year|C+[/TD]
[/TR]
[TR]
[TD]1970[/TD]
[TD]BB[/TD]
[TD="align: left"]1 year up to 3 Years[/TD]
[TD]707[/TD]
[TD="align: left"]1 year up to 3 Years|BB[/TD]
[/TR]
[TR]
[TD]9286[/TD]
[TD]B+[/TD]
[TD="align: left"]1 year up to 3 Years[/TD]
[TD]87[/TD]
[TD="align: left"]1 year up to 3 Years|B+[/TD]
[/TR]
</tbody>[/TABLE]

My goal is to make a Pivot table to see where and to what degree do the investments deviate from the Limits as they are explained in the first table.
I was able to build this Pivot by putting the” max Aggregate Sum” in the row label of the pivot:


[TABLE="width: 599"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]I Want to Create this Column Whit PowerPivot[/TD]
[/TR]
[TR]
[TD="align: left"]Term[/TD]
[TD="align: left"]Rating[/TD]
[TD="align: left"]MAX Aggregate Sum[/TD]
[TD="align: left"]Sum of sum[/TD]
[TD="align: left"]MAX Aggregate Sum - Sum of sum[/TD]
[/TR]
[TR]
[TD="align: left"]up to 1 Year[/TD]
[TD="align: left"]A+[/TD]
[TD="align: left"]800[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]767[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: left"]AA[/TD]
[TD="align: left"]1000[/TD]
[TD="align: right"]551[/TD]
[TD="align: right"]449[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: left"]C+[/TD]
[TD="align: left"]0[/TD]
[TD="align: right"]731[/TD]
[TD="align: right"]-731[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: left"]CC[/TD]
[TD="align: left"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]-100[/TD]
[/TR]
[TR]
[TD="align: left"]up to 1 Year[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1415[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]1 year up to 3 Years[/TD]
[TD="align: left"]AA[/TD]
[TD="align: left"]900[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]825[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: left"]B+[/TD]
[TD="align: left"]50[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]-37[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: left"]BB[/TD]
[TD="align: left"]200[/TD]
[TD="align: right"]707[/TD]
[TD="align: right"]-507[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: left"]C+[/TD]
[TD="align: left"]0[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]-50[/TD]
[/TR]
[TR]
[TD="align: left"]1 year up to 3 Years[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]919[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]3 Years up to 5 Years[/TD]
[TD="align: left"]B+[/TD]
[TD="align: left"]10[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]-60[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: left"]BB[/TD]
[TD="align: left"]100[/TD]
[TD="align: right"]662[/TD]
[TD="align: right"]-562[/TD]
[/TR]
[TR]
[TD="align: left"]3 Years up to 5 Years[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]732[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]more then 5 Years[/TD]
[TD="align: left"]A+[/TD]
[TD="align: left"]500[/TD]
[TD="align: right"]422[/TD]
[TD="align: right"]78[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: left"]BB[/TD]
[TD="align: left"]0[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]-40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: left"]CC[/TD]
[TD="align: left"]0[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]-366[/TD]
[/TR]
[TR]
[TD="align: left"]more then 5 Years[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]828[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Total[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3894[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I’m experiencing a problem when trying to build a correct measure to subtract the investment from the deviations (without over-summing the deviations).
Can anybody help?
Thanks in advance.

 

[scottsen]

I'm not quite getting your question. Did you create "MAX Aggregate Sum - Sum of sum" directly "in excel" and want it to be a real powerpivot measure?

 

[MyAtHome]

hi scottsen, exactly, I did (only) that column "in excel" and want it to be a real powerpivot measure

 

[scottsen]

A measure (calculated field in 2013) something like this should give what you want:

=MAX(FirstTable[Max Aggregate Sum]) - SUM(SecondTable[Sum])

 

[MyAtHome]

thanks a lot , it works (in 2010, after bulding the max measure separately and then bulid a measure to subtract the columns using that max measure).
I have a followup question, if I have a situation were, if a lower limit hasn't been used then it is Ok to use that limit to extend a higher limit as long as the total invesmnet sum hasnt exceeded the highest limit (in my example the highest limit is a 1000 for AA rating up to 1 year)
how can I use PowerPivot to chack fot that?

 

[scottsen]

The terminology is throwing me a bit, but I think you are saying "if the max aggregate sum is 0, pretend it is 1000" ?

You could do that 2'ish ways:
1) add a new calculated column, maybe called "Effective Max Aggregate Sum" = IF (Table1[Max Aggregate Sum] = 0, MAX(Table1[Max Aggregate Sum]), Table1[Max Aggregate Sum]) ... then use that instead of your other max agg sum.
2) do something similar, but in your max measure:
instead of =MAX(FirstTable[Max Aggregate Sum])
=IF (MAX(FirstTable[Max Aggregate Sum]) = 0, CALCULATE(MAX(FirstTable[MAX Aggregate Sum]), ALL(FirstTable)), MAX(FirstTable[Max Aggregate Sum])))

But, not totally sure i got your question

 

[MyAtHome]

Hi,
Thanks for your quick response. I guess I wasn’t clear, let me try and clarify my follow-up question:
The first table represents the limits for investments (for both the rating and the term of the aggregate investments). The second table represents the actual investments.
What I want to do is to create a measure that will check if in fact there is a deviation in the actual investments, given that:
1. The maximum aggregate sum for all terms and rating is represented by the highest value in the first table. In this example: rating AA, up to 1 year, aggregate sum of 1000. That means that the total of actual investments (Second Table) can’t exceed that limit.
2. If there is a record with zero limit value in the aggregate max sum in the first table, then if there is any record in the Second Table with a positive sum value of that same rating and term, then this would be considered a deviation. For example: in the first table for a rating of BB and term of more than 5 years the limit is zero. Because in the second table of actual investments there is at least one record with positive value for that rating and term, the measure will show a deviation for that term and rating.
3. The first table is built in a hierarchal form (high to low). If for a given term and rating limit, the sum for the same rating and term in the actual investment table (second table) hasn’t exceeded the limit, then I would like to allow the difference (the amount not used) to be added to the higher rating term limits (in the first table) – as long as abovementioned points 1 and 2 are valid. For example: if the sum of investments for a rating of ”B+” with a term of ” 1 year up to 3 Years” is only 10 (even though the limit is 50) then a deviation of up to 40 in a higher term rating investment would be allowed and not considered as a deviation .

Thanks again!

 

[scottsen]

There is a lot going on here, we better just take them one at a time. Otherwise our brains will probably explode

" if there is any record in the Second Table with a positive sum value of that same rating and term, then this would be considered a deviation."

=COUNTROWS(FILTER(VALUES(SecondTable[TermAndRating]), [Total Value] > 0))

Drop that measure on rows and see if it does what you want

 

[MyAtHome]

Unfortunately my computer perished , I will come back after my tech guy will fix it.

Thanks again