Hi Pieter,

Welcome to our forum! Thanks for sharing your sample file. Please see the solution in the attached file which uses the IF formula you created (renamed _Small Club, in the following formula:

=CALCULATE(DISTINCTCOUNT(Table1[Town]),FILTER(Table1,[_Small Club]))

Mynda

Hi Mynda,

Thanks very much for such a quick and helpful reply - really appreciated.  Boy, do I have a lot to learn...

Pieter

... 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 Pieter,

Not sure. You'd have to test it on your dataset. It's often easier to follow the formulas when you break it out into its separate components.

Mynda

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 Pieter,

I think it's time to learn Power Pivot 🙂 I can see from your new table and subsequent PivotTable that you don't understand how to create a model with the necessary dimension tables and then how to use the fields from those tables in your PivotTable.

You need a dimension table for the area and town. You then create relationships from the memberships and secs tables to the dimension table. You then use the fields from that dimension table in your PivotTable and measures.

Hope that points you in the right direction. If you're keen to learn Power Pivot and fill in the blanks in your knowledge, please consider my course.

Mynda

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 Pieter,

You can't auto generate the dimension table with Power Pivot, but you can use Power Query to automate it based on the values in the fact tables. You can get my Power Query course with my Power Pivot course and the shopping cart will automatically take 20% off! 😉

Mynda

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 Pieter,

That still doesn't sound right. Please try attaching your workbook again. You need to click the yellow 'start upload' button.

Mynda

Hi Mynda,

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

Pieter

Hi Pieter,

You're right, you didn't structure your model correctly. The Towns table (your fact table) should contain the Area ID column. That makes the Alloc table redundant. Then you can relate the Towns and Areas tables together to use the AreaName field in your PivotTables.

Note: typically you don't put numeric columns like SmallClub and ClubSize in the row labels.

See file attached. Hope that points you in the right direction.

Mynda

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 Pieter,

In that case, make the Towns table your fact table and remove the AreaID and Town Name columns. And make the Dim Alloc table also contain the Area name and Town name then create a relationship between the Towns and Alloc tables via the TownID field. Remove the Area table.

Mynda