Subtotalling IF and COUNT measures|Power Pivot|Excel Forum|My Online Training Hub

... and a follow-up question, please!  Is there any performance advantage in keeping the two formulas separate or would it be better to combine them?  Mamy thanks again.

Pieter

Hi Mynda,

I'm afraid I've broken it!  I added a second table for club secretary details and set up a relationship on field "Town" to link the two.  However, the pivot now shows every Secretary against every club, which looks as though it's not recognizing the relationship.  Would you please be able to point me in the right direction?

Many thanks again.

Pieter

Hi Mynda,

You're absolutely right - I do need to learn Power Pivot!  As it happens, I was presented with the original table of several thousand lines as a fait accompli and asked to summarize it to show small clubs, which I did - with your help (above).  I then wanted to improve the result by including the secretary's details on the summary, which is why I brought in the second table.  From what you say, it looks as though I first need to split out a dimension table from the original table and will have to do this every time the original data changes.  I'll try to figure out a way to do this from within PP so that it doesn't have to be done manually each time the data changes!

Very many thanks for pointing me in the right direction ... and I'll check out the course.

Pieter

Hi Mynda,

I followed your advice and learned a lot in the process.  I've re-worked my data into normalised related dimension and fact tables (I think!), though some of the fact tables contained numerical data.  I spent a lot of time trying to work out why relasted tables didn't work in a related way when adding a value field.  I mean, the rows looked fine and showed the related data, but when adding a value field the whole thing went un-related and I got the dreaded "yellow warning". 

After some research I realized that I needed to bring the related data into the dimension table using RELATED, but this seems like a real sledgehammer-to-crack-a-nut approach.  Is this really the simplest way to achieve the required result?  I've attached a workbook showing the resultant pivot table, which includes the subtotalled columns.

Pieter

Hi Mynda,

Apologies and thanks for your patience.  Here it is, I hope.

Pieter

Hi Mynda,

I completely take your point about the table structures - my reason for the Alloc[ation] table was that it is sometimes necessary to switch a town to a different area and that just seemed a cleaner way of doing it.

I also understand that numeric columns don't usually go in row labels; in this case I was asked to treat the Clubsize as a Town Fact, so that seemed a good place to put it ... which is where I ran into the need to use "RELATED" to connect the records, even though the tables were related.  I'll now work on a tidier version that determines each club's size by the membership records, which is where we first started all this!  Thanks very much for helping to get my thoughts properly organized.  Gretings from a very warm English south coart!

Pieter

Hi Mynda,

I've been doing quite a lot of thinking about this and I'm really grateful for your insights and suggestions.  I reproduced the pivot that you sent a picture of and all was well, but I then started to do some thinking about it.  If the Towns table is the fact table, as it should be, then surely referential integrity requires it to be on the "one" side of the relationship, which isn't the case (see pic)

As soon as I switched the relationship around, the pivot broke (of course) and the same figure (642) was repeated in every cell of Sum of Club Size.  In order to reapir this, I had to create a calculated column using RELATED to bring that field across to the dimension table.  This became more obvious when I wanted to expand the tables by including extra facts for each Town and Area (see attached workbook).  This works, of course, as the pivot table is effectively just working on a single PowerPivot Table.

I'vecome to realise that Power Pivot relationships aren't the same as relational database relationships and I'll continue my journey.  Sorry this has been going round in circles and I'll quite understand if you've had enough of this conversation.  However, I'd be grateful if you could let me know if I'm now thinking along the right lines.

Thanks again.

Pieter

Hi Mynda,

Don't know what was going on in my head to get Fact and Dimension tables 180 degrees wrong!  I've given myself a stern talking to and have created a small star relationship table, with tblAlloc as the Fact, and the others as Dimension tables, with the filter arrows all going the right way.  I also have re-done my measures without a single RELATED field to be seen; the result (attached) may not be the most efficient in terms of DAX, but it's given me wht I needed - as well as a lot of learning.

Many thanks again for your persistence.

Pieter

Hi Mynda,

First a huge apology for upsetting you and I recognize that some of my questions seem contradictory – and sometimes just wrong.  I really wasn’t setting out to be difficult, it’s just that as I developed my very basic understanding, a number of different issues came up.

My first question was to ask how I could get a running set of subtotals for club numbers and the number of small clubs, based on a single table containing club memberships, with some members belonging to more than one club.  You kindly helped with this and provided an excellent solution.

Things developed and I should probably have set up a completely different situation, but I didn’t.  I wanted to be able to include multiple pieces of information specific to each town and area, and link them together.  I therefore put aside the “memberships” issue and included each town’s club size as a given number, as well as additional specific information – in this case, the Secretary’s name.  To balance things up, I also included the Area Secretary’s name.

I thus needed to glue together (allocate) towns and areas, noting that a Club may move from one area to another.

Your solution worked absolutely fine, and it seemed to me that your solution involved a bit of duplication, such as the Town name, Area name and Secretary name on each row of the alloc table.

It was at this point that I made my foolish mistake in getting “Fact” and “Dimension” tables mixed up!  I was also trying to separate out the information specific to each to Town (tblTowns) and to each Area (tblAreas).  From my earlier experience with databases, some years ago, it seemed reasonable to assign a primary key to each Town (TownID) and each Area (AreaID).  The idea was to simply enter the related, foreign, key into tblAlloc to draw together the two sets of information.  In this example the club size was actually a dimension of each club – perhaps not strictly correct, but it was specific to each club.

In the back of my mind I was then looking at adding the date of each (re)allocation to tblAlloc, together with, perhaps, any costs associated with the reallocation.  I was thinking of sports clubs, transfers etc.  This would have made tblAlloc more obviously a Fact table, with every Town transfer from one area to another, on a particular date, representing a single transaction that could be analysed.

Once again, I’m sorry to have caused frustration and I hope you’ll forgive me.

Pieter