Power Pivot Table: how to total the number of rows within a column that contain data?

[Janell]

I understand that this will probably require some kind of Dax expression in the data model. I have little to no experience with DAX, but I'm willing to learn.

Basically, I want to count a Group of Payees if any one of the Payees has received an invoice for that year, even if the amount in that invoice equals zero. One of the problems is that my main table (MEGA) with all the date data and payee numbers does not have the Groups, which lives in a secont related table (UNIQUE_PAYEE) with the Payee to Group relationship (there can be many Payees in a single Group). So a unique count of Groups refuses to filter by year, only a unique count of Payees or a unique count of Year, but I want to then relate them back to the Groups and then count those instead.

Distinct count of Years shows a 1 next to each Group (which is correct), but then doesn't sum the number of instances and displays a 1 as the grand total because there is only 1 year per year (duh) and I can't get it to count the number of instances in the power pivot column instead (see the attached picture for the idea).

Distinct count of Payees shows multiple payees next to each group, but I can't for the life of me make it display only 1 if it's more than 1 number and THEN get summed up.

Distinct count of Group shows a 1 next to each group name, regardless of if it showed up for that year. If I use the filter value option for the power pivot table for the Distinct count of Payee or Year to not be 0, AND filter it to only 1 year, then I can get the right total, but once I'm showing ALL years, it goes to **bleep** again, and so I can't make a Pivot Chart and show the growth over time.

I've spent hours coming up with convoluted formulas that all end up at the exact same dead ends. Help! - Janell

[ABD128 · Accepted solution]

Hey @Janell 

The current issue arises from how your Pivot Table is counting values. At present, it either provides a distinct count of Years or Payees, which does not accurately reflect the number of Groups with activity in a given year. The required approach is to count a Group once per year if at least one Payee in that Group has an invoice, including cases where the invoice value is zero. Since Groups are stored in a separate table, a standard distinct count does not yield the correct result—it will display “1” for each row and again in the grand total. To resolve this, you should create a measure that examines the relationship between the invoice table and the Groups table, flagging a Group as active for the year if any Payee has data. This ensures each active Group is counted once per year, and the grand total accurately reflects all active Groups by year. Implementing this method will allow your Pivot Table and chart to display the correct number of Groups over time, without the need for complex filters or manual adjustments.

Regards,

ABD

[ABD128 · Accepted solution]

Hey @Janell 

The current issue arises from how your Pivot Table is counting values. At present, it either provides a distinct count of Years or Payees, which does not accurately reflect the number of Groups with activity in a given year. The required approach is to count a Group once per year if at least one Payee in that Group has an invoice, including cases where the invoice value is zero. Since Groups are stored in a separate table, a standard distinct count does not yield the correct result—it will display “1” for each row and again in the grand total. To resolve this, you should create a measure that examines the relationship between the invoice table and the Groups table, flagging a Group as active for the year if any Payee has data. This ensures each active Group is counted once per year, and the grand total accurately reflects all active Groups by year. Implementing this method will allow your Pivot Table and chart to display the correct number of Groups over time, without the need for complex filters or manual adjustments.

Regards,

ABD

[Janell · Accepted solution]

Hiya! I used your description in chatgpt to help me build the DAX measure (in case someone else is searching for this kind of answer.) Note that I had already created a relationship between the main table's list of Payees and the Group's list of Payees. Remove the quotation marks to use:

"Active Groups:=COUNTROWS (FILTER (VALUES (Unique_Payees[Group]), --a separate table that has the Group for each of the payee numbers CALCULATE ( COUNTROWS ( 'All Time'), -- All Time is the main table with the payee numbers and the dates but not the Groups ALLEXCEPT (Unique_Payees, Unique_Payees[Group]) -- ignore Payee filters, keep Group filter) > 0))"