• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

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

You are here: Home / Subtotalling IF and COUNT measures|Power Pivot|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower PivotSubtotalling IF and COUNT measures
sp_PrintTopic sp_TopicIcon
Subtotalling IF and COUNT measures
Page: 12Jump to page
Avatar
Pieter Cox
Member
Members

Power Query
Level 0
Forum Posts: 74
Member Since:
August 3, 2020
sp_UserOfflineSmall Offline
1
August 3, 2020 - 12:21 am
sp_Permalink sp_Print

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.

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
August 3, 2020 - 2:18 pm
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
Avatar
Pieter Cox
Member
Members

Power Query
Level 0
Forum Posts: 74
Member Since:
August 3, 2020
sp_UserOfflineSmall Offline
3
August 3, 2020 - 5:12 pm
sp_Permalink sp_Print

Hi Mynda,

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

Pieter

Avatar
Pieter Cox
Member
Members

Power Query
Level 0
Forum Posts: 74
Member Since:
August 3, 2020
sp_UserOfflineSmall Offline
4
August 3, 2020 - 6:53 pm
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
5
August 3, 2020 - 7:35 pm
sp_Permalink sp_Print

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

Avatar
Pieter Cox
Member
Members

Power Query
Level 0
Forum Posts: 74
Member Since:
August 3, 2020
sp_UserOfflineSmall Offline
6
August 5, 2020 - 2:32 am
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
7
August 5, 2020 - 8:22 am
sp_Permalink sp_Print

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

Avatar
Pieter Cox
Member
Members

Power Query
Level 0
Forum Posts: 74
Member Since:
August 3, 2020
sp_UserOfflineSmall Offline
8
August 5, 2020 - 5:26 pm
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
9
August 5, 2020 - 9:18 pm
sp_Permalink sp_Print

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

Avatar
Pieter Cox
Member
Members

Power Query
Level 0
Forum Posts: 74
Member Since:
August 3, 2020
sp_UserOfflineSmall Offline
10
August 7, 2020 - 3:56 am
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
11
August 7, 2020 - 8:37 am
sp_Permalink sp_Print

Hi Pieter,

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

Mynda

Avatar
Pieter Cox
Member
Members

Power Query
Level 0
Forum Posts: 74
Member Since:
August 3, 2020
sp_UserOfflineSmall Offline
12
August 7, 2020 - 4:54 pm
sp_Permalink sp_Print

Hi Mynda,

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

Pieter

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
13
August 9, 2020 - 1:51 pm
sp_Permalink sp_Print

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

Avatar
Pieter Cox
Member
Members

Power Query
Level 0
Forum Posts: 74
Member Since:
August 3, 2020
sp_UserOfflineSmall Offline
14
August 10, 2020 - 12:59 am
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
15
August 10, 2020 - 8:47 am
sp_Permalink sp_Print sp_EditHistory

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

pieter_pp_pt.pngImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage pieter_pp_pt.png (229 KB)
Avatar
Pieter Cox
Member
Members

Power Query
Level 0
Forum Posts: 74
Member Since:
August 3, 2020
sp_UserOfflineSmall Offline
16
August 11, 2020 - 7:43 am
sp_Permalink sp_Print

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)Towns-and-alloc.pngImage Enlarger

.

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

sp_PlupAttachments Attachments
  • sp_PlupImage Towns-and-alloc.png (9 KB)
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
17
August 11, 2020 - 8:55 am
sp_Permalink sp_Print

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

Avatar
Pieter Cox
Member
Members

Power Query
Level 0
Forum Posts: 74
Member Since:
August 3, 2020
sp_UserOfflineSmall Offline
18
August 12, 2020 - 2:44 am
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
19
August 12, 2020 - 8:39 am
sp_Permalink sp_Print

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

Avatar
Pieter Cox
Member
Members

Power Query
Level 0
Forum Posts: 74
Member Since:
August 3, 2020
sp_UserOfflineSmall Offline
20
August 12, 2020 - 9:27 pm
sp_Permalink sp_Print

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

Page: 12Jump to page
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Velouria, Jack Brett, Ivica Cvetkovski
Guest(s) 12
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Hayden Hao
Angela chen
Sean Moore
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Forum Stats:
Groups: 3
Forums: 24
Topics: 6211
Posts: 27239

 

Member Stats:
Guest Posters: 49
Members: 31891
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.