求适配Excel的DAX公式：实现捐赠者年龄范围去重

Hey Roger, let's work through this problem where your pivot table's total donor count doesn't match up, even though donation amounts are correct. The root cause is clear: some donors cross age ranges within the year, so each of their donations gets tagged to a different range, making them count multiple times. Your original DAX works in DAX Studio, but Excel blocks it because of its strict rules around SUMMARIZECOLUMNS and filter contexts. Let's fix that with Excel-compatible DAX solutions.

Why Your Original Code Fails in Excel #

Excel's DAX engine doesn't let SUMMARIZECOLUMNS reference external filter contexts (like your fc variable with date filters). DAX Studio is more flexible here, but Excel needs a different approach using functions that play nice with its filter logic.

Solution 1: Keep the Age Range with the Most Donations (Highest Proportion) #

This method prioritizes the age range where the donor gave the most times. If there's a tie, it picks the highest age range (you can tweak this if needed).

Step 1: Create Supporting Calculated Tables #

First, count how many times each donor used each age range:

DonorAgeRangeCounts = 
SUMMARIZE(
    data_table,
    data_table[Donor_ID],
    data_table[summary_range],
    "DonationCount", COUNT(data_table[Donation_ID])
)

Next, rank these ranges for each donor by donation frequency:

DonorAgeRangeRanked = 
ADDCOLUMNS(
    DonorAgeRangeCounts,
    "Rank", 
    RANKX(
        FILTER(DonorAgeRangeCounts, [Donor_ID] = EARLIER([Donor_ID])),
        [DonationCount],, DESC, DENSE
    )
)

Finally, filter down to only the top-ranked range per donor:

DonorUniqueAgeRange = 
FILTER(
    DonorAgeRangeRanked,
    [Rank] = 1
)

Step 2: Mark Primary Ranges in Your Original Table #

Add a calculated column to your data_table to flag which rows belong to the donor's primary age range:

IsPrimaryAgeRange = 
VAR CurrentDonor = data_table[Donor_ID]
VAR CurrentRange = data_table[summary_range]
VAR TopRange = MAXX(FILTER(DonorUniqueAgeRange, [Donor_ID] = CurrentDonor), [summary_range])
RETURN CurrentRange = TopRange

Now, when building your pivot table, add a filter for IsPrimaryAgeRange = TRUE—this will count each donor only once, using their most frequent age range.

Solution 2: Keep the Age Range from the Last Donation (Simpler, Arbitrary Pick) #

If you don't need to prioritize by donation count, just pick one range (like the last time the donor gave), use this simpler approach:

First, get each donor's last donation date:

DonorLastDonation = 
SUMMARIZE(
    data_table,
    data_table[Donor_ID],
    "LastDonationDate", MAX(data_table[Donation_Date])
)

Then add a calculated column to flag the row with that last date:

IsLastDonationAgeRange = 
VAR CurrentDonor = data_table[Donor_ID]
VAR LastDate = MAXX(FILTER(DonorLastDonation, [Donor_ID] = CurrentDonor), [LastDonationDate])
RETURN data_table[Donation_Date] = LastDate

Filter your pivot table to IsLastDonationAgeRange = TRUE, and you'll have one entry per donor.

Both of these methods work seamlessly in Excel, no more SUMMARIZECOLUMNS errors. Pick the one that fits your business needs best!

内容的提问来源于stack exchange，提问作者Roger