Spectrum repoerting via powerpivots

Resolved · Medium Priority · Version 2013

Mon 26 Mar 2018, 12:07 Delegate Lloyd said...

Lloyd has attended:
Excel PowerPivot course

Spectrum repoerting via powerpivots

Hi,
I am working with a database of plus 80k client loan records which includes the following data fields: Client Codes, Distribution area, type of equipment loaned, loan quantity, date of loan issue.

Using PowerPivot reporting, how do I produce a chart showing the spectrum of how many clients within a period receiving the same number of equipment loans?

For example:
Clients with single loan - 15000
Clients with 2 loans - 30000
clients with 3 loans - 25000 etc etc.

I have tried adding a table of all individual clients and have successfully formulated a column showing the number of loans for each client. However, as the Dax measures/formulas are outside of the client transactional table, I do not get the analysis when I drop the formulas into a pivot.

Is this kind of spectrum result possible with powerpivots?
If so what formulas do I use?

Mon 26 Mar 2018, 14:09 Trainer Jens said...

RE: spectrum repoerting via powerpivots

Hi Lloyd,

Thank you for the forum question.

Create a calculated column in your table.

If your table is called ClientLoan the DAX formula in the calculated column should look like below:

=COUNTX(FILTER (ClientLoan, EARLIER (ClientLoan[Client Codes] ) = ClientLoan[Client Codes] ),ClientLoan[Client Codes])

The column will now show the frequency.

Create a PivotTable and put the above calculated column as ROWS and CLIENT CODES as VALUES and make sure that client codes are counted.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 26 Mar 2018, 15:21 Trainer Jens said...

RE: spectrum repoerting via powerpivots

Hi Lloyd,

Sorry I was to fast with my answer.

You will need one more measure:

In the measure area under the table:

DistinctClientCode:=Distinctcount([Client Codes])

and use this measure as values in the PivotTable.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Tue 27 Mar 2018, 17:45 Delegate Lloyd said...

RE: spectrum repoerting via powerpivots

Hi Jens,

I did manage to work it out in the end, so sorry I was perhaps too quick to ask. I was about to state 'no need to reply'. Sometimes I approach a task and overcomplicate the thinking behind it. I did it differently from what you suggested. I created a calculated column in my table, and then brought the values of that calculated column into the transactional table using the lookup value function. Yes I did use the distinct client count measure to drop into the pivot box. That's where my silly thinking had started - I was thinking how to apply a distinct filter to the calculated column of loan totals for each client!

Thank you anyway

Lloyd

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

New Normal Worksheet

Do you want all your worksheets to confirm to a certain look? Then change the Defaults!!!
1. Press Shift+F11 to create a new worksheet
2. Press Ctrl+A to select (higlight) all cells, Press Ctrl+1, make any formatting changes then click OK.
3. Press F12 (Function 12 key) click in the Save As Type, drop down, then select Template (*.xlt)
4. Click in the Save in drop-down, then find the folder; c:_program files_microsoft office_office_start. (For the underscores shown use backslash)
Name your templete sheet.xlt, then press Enter.
Sheet.xlt is used when you insert a new worksheet (Shift+F11)

Note: These changes are permanent changes on your PC.

View all Excel hints and tips