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?
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
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
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
