Log In
Register
Home
August 3, 2020
Offline
Hello,
I'm just starting my PQ journey. I have attached a sample workbook, which shows club memberships. Each row is a membership; there are six towns with clubs, grouped into three areas and a member can join more than one club. I have a value column for the count of membershipIDs, which gives the number of members for each club. If a club has three or fewer(!) members it's classed as "small" and I've created a measure to give 1 for a small club, otherwise 0, using
=if(COUNT([MemberID])>3,0,1)
So far, so good.
I've subtotalled the pivot table by area and want to show the number of small groups in each (ie the count of measure "Small Club"). However, the "Summarize values by" option is greyed out for that column and it doesn't allow me to show the sum. I've checked and I think all values in that column are set to "number" format.
I'm sure I've missed something and should welcome some help! Many thanks.
See Answer
July 16, 2010
Offline
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
Answers Post
August 3, 2020
Offline
Hi Mynda,
Thanks very much for such a quick and helpful reply - really appreciated. Boy, do I have a lot to learn...
Pieter
August 3, 2020
Offline
... 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
July 16, 2010
Offline
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
August 3, 2020
Offline
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
July 16, 2010
Offline
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
August 3, 2020
Offline
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
July 16, 2010
Offline
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
August 3, 2020
Offline
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
July 16, 2010
Offline
Hi Pieter,
That still doesn't sound right. Please try attaching your workbook again. You need to click the yellow 'start upload' button.
Mynda
August 3, 2020
Offline
Hi Mynda,
Apologies and thanks for your patience. Here it is, I hope.
Pieter
July 16, 2010
Offline
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
August 3, 2020
Offline
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
July 16, 2010
Offline
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
August 3, 2020
Offline
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
July 16, 2010
Offline
Hi Pieter,
You've got yourself confused. The dimension tables filter the fact tables, therefore the relationship was correct because the Alloc dim table filtered the Towns fact table. The direction of the arrow on the connector line indicates the filter direction.
Mynda
August 3, 2020
Offline
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
July 16, 2010
Offline
Hi Pieter,
While it may work, this is fundamentally the wrong approach. Fact tables have values, dim tables typically don't. You're forcing the Alloc table to be a fact table by assigning the measures to it. That doesn't make it a fact table.
This incorrect structure is forcing you to create measures that you don't even need. If you just follow my advice and structure your model the way I've shown you (see attached again with the new SecName field) it will be so much easier for you, and will prevent you running into problems further down the track as your model evolves.
I feel like we're going around in circles. I show you how to structure it, you come back with the opposite. I show you again how to structure it and yet again you come back with the opposite. Perhaps there is a bigger picture you're not sharing with me that justifies why you're not taking my advice. If so, please share so I can give you the right advice the first time.
Mynda
August 3, 2020
Offline
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
1 Guest(s)
