Grouping Contracts by Revenue

[JEB85]

Hi Guys</SPAN>

I have a huge transactional data set held in PowerPivot that details our revenue for the contracts we have over a period of time. What I’m trying to do is group the contracts by revenue in blocks of 100 (largest to smallest). My desired result would be something like the following:</SPAN>

[TABLE="width: 233"]
<TBODY>[TR]
[TD]No. Contracts</SPAN></SPAN>
[/TD]
[TD]Revenue</SPAN></SPAN>
[/TD]
[TD]% Total</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]1-100</SPAN></SPAN>
[/TD]
[TD]15,000</SPAN></SPAN>
[/TD]
[TD]49.0%</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]101-200</SPAN></SPAN>
[/TD]
[TD]8,000</SPAN></SPAN>
[/TD]
[TD]26.1%</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]201-300</SPAN></SPAN>
[/TD]
[TD]4,000</SPAN></SPAN>
[/TD]
[TD]13.1%</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]301-400</SPAN></SPAN>
[/TD]
[TD]2,000</SPAN></SPAN>
[/TD]
[TD]6.5%</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]401-500</SPAN></SPAN>
[/TD]
[TD]1,000</SPAN></SPAN>
[/TD]
[TD]3.3%</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]501+</SPAN></SPAN>
[/TD]
[TD]600</SPAN></SPAN>
[/TD]
[TD]2.0%</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30,600</SPAN></SPAN>
[/TD]
[TD]100.0%</SPAN></SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

In other words what I want to say is the largest 100 contracts contribute revenue totalling 15,000, the 101^st to 200 largest contracts total revenue of 8,000 etc.</SPAN>

The field for my ‘Contracts’ comes for the ‘AllContracts’ table. I have a measure for ‘Revenue’ in place already.</SPAN>

Is what I’m trying to achieve possible through Powerpivot? (In regular Excel I could of course just use SUMIFS or SUMPRODUCT but my data set is huge so is held in PowerPivot).</SPAN>

 

[miguel.escobar]

if I understood currectly, then this blog post will help you:
http://www.mrexcel.com/forum/powerp...-linked-table-info-used-calculate-column.html (case given and currently workinh on it)
Alberto Ferrari : Banding with PowerPivot

 

[JEB85]

Hi Miguel,

Thanks for the post - will definitely be useful in the future.

With a really simple example I should be able to show what I'm trying to do:

DATA SET

[TABLE="width: 190"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Contract[/TD]
[TD]Revenue[/TD]
[TD]Rank by Total Revenue[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]618[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]636[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]773[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]519[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]701[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]707[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]626[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]513[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]511[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]887[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]938[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]967[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]902[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]555[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]947[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]847[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]501[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]557[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]621[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]574[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]906[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]854[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]613[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]598[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]572[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]690[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]929[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]705[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]530[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Desired Result

[TABLE="width: 317"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]No. Contracts[/TD]
[TD]Revenue[/TD]
[TD]% Total[/TD]
[/TR]
[TR]
[TD]1-2[/TD]
[TD="align: right"]9,462[/TD]
[TD="align: right"]45.28%[/TD]
[/TR]
[TR]
[TD]3-4[/TD]
[TD="align: right"]5,015[/TD]
[TD="align: right"]24.00%[/TD]
[/TR]
[TR]
[TD]5-6[/TD]
[TD="align: right"]3,852[/TD]
[TD="align: right"]18.43%[/TD]
[/TR]
[TR]
[TD]7-8[/TD]
[TD="align: right"]2,568[/TD]
[TD="align: right"]12.29%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]20,897[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
</tbody>[/TABLE]

So here I'm saying the 2 biggest contracts total 9,462 Revenue, the third and fourth biggest contracts total 5,015 revenue etc.

Now obviously the above is a simple data set - my data set is huge and I'd like to be able to group the contracts into 100s up to 500 and then group everything over and abover 501.

I'm sure there must be a powerpivot solution? I guess I would need to somehow rank my data through DAX first off?

 

[miguel.escobar]

You'd have to create a measure for each range if you want to use it as a measure and then move the values to the rows so they can show as you want them to. The RANKX function can not be used as a boolean in CALCULATE and I'm trying to find out the best way to do it (still thinking about it).

I was thinking about using the TOPN like

=CALCULATE(SUM(Table1[Revenue]),TOPN(2,SUMMARIZE(Table1,Table1[Contract],"Sum",SUM(Table1[Revenue])),[sum]))

=CALCULATE(SUM(Table1[Revenue]),TOPN(4,SUMMARIZE(Table1,Table1[Contract],"Sum",SUM(Table1[Revenue])),[sum]))
- CALCULATE(SUM(Table1[Revenue]),TOPN(2,SUMMARIZE(Table1,Table1[Contract],"Sum",SUM(Table1[Revenue])),[sum]))

but that's going to take you a really big chunk of your memory allocation. I believe there's a way of doing this with the SUMX function but the trick goes when taking in consideration the ranking context.

I'll try to see this case on the weekend....sounds like a fun scenario!
Miguel

 

[AlbertoFerrari]

Isn't it a variation on the ABC Pareto analysis pattern?
You will find a complete explanation here: SQLBI - Marco Russo : ABC Analysis in PowerPivot

Alberto
PowerPivot Workshop - The first PowerPivot course, produced by SQLBI - Tweet #ppws

 

[miguel.escobar]

as Alberto says, it's way easier if you try the calculated column within the powerpivot grid approach.

Check out this file that I've created with the solution to this case
https://dl.dropbox.com/u/54063091/Contracts.xlsx

I wish that Powerpivot had the LARGE function as it would make this case WAY much easier.

Miguel

 

[JEB85]

Thanks guys.

I'm going to take a look at this next week - I agree the large function in PowerPivot would be extremely useful!