Help getting table total to show total of individual rows

[Anonymous]

I am struggling with the common "total in table does not match the total of individual rows" issue. Have tried a number of the solutions on here but cant get any to work for my specific situation.

I need to calculate the total upsell on a customer by customer basis for one month compared to the previous month, but only include positive variances.

I have sales table (MRR) which has customerID, product type, date of sale, value of sale. I have separate measures for total sales in the current month (MRR end of period) and the previous month (MRR end of previous period) which seem to work fine

My DAX formula is as follows:

Upsell = SUMX (VALUES (MRR[CustomerID]), MAX([MRR End of period] - [MRR End of previous period],0))

I then put this in a table with a month column to show the total upsell in each month. It calculates correctly for each row in the table but the total is not the sum of the individual months. I know the reason for this (because the context has shifted from row context to the entire table) but I can't seem to derive an amended formula that gives the total I want. How do i fix it?

[Poojara_D12]

Hi @Anonymous 

The issue you're facing with the total in your Power BI table not matching the sum of individual rows is a common DAX context problem. This occurs because, while your measure correctly calculates the upsell per customer at the row level, the total row evaluates the formula in a different context, applying the calculation to the entire dataset rather than summing up pre-calculated row values. Your current measure, SUMX(VALUES(MRR[CustomerID]), MAX([MRR End of period] - [MRR End of previous period],0)), attempts to compute the upsell by iterating over unique CustomerIDs. However, at the total level, VALUES(MRR[CustomerID]) returns all customers in the filter context, causing an incorrect evaluation. To fix this, you need to ensure that the total is a summation of individual positive upsell values, rather than a recalculated difference across all customers. A better approach is to use SUMX(VALUES(MRR[CustomerID]), IF([MRR End of period] - [MRR End of previous period] > 0, [MRR End of period] - [MRR End of previous period], 0)), which explicitly filters out negative values before summing them up. Alternatively, using SUMX(SUMMARIZE(MRR, MRR[CustomerID], "@Upsell", [MRR End of period] - [MRR End of previous period]), IF([@Upsell] > 0, [@Upsell], 0))  ensures that each customer’s upsell is first calculated individually before being aggregated. This method prevents the total row from recalculating based on a broader dataset context and instead sums the correct row-level values, resulting in an accurate total upsell figure for each month.

[johnt75 · Accepted solution]

Its not enough to do a SUMX over the customers, you need to take the individual months into account as well so that for every month you are iterating over the customers for that month.

You can do that by creating a summary table which breaks your sales down by both month and customer, and then iterate over that.

Upsell =
VAR SummaryTable =
    SUMMARIZE ( MRR, MRR[CustomerID], 'Date'[Year month] )
VAR Result =
    SUMX (
        SummaryTable,
        MAX ( [MRR End of period] - [MRR End of previous period], 0 )
    )
RETURN
    Result

[Anonymous]

Thanks, this work. I have a follow up question...

In the source data (which is just an excel spreadsheet) there may be blank cells for customers in certain months (i.e. there were no sales). Thus [MRR End of period] and [MRR End of previous period] may be blank

If I create a measure = MIN([MRR End of period] - [MRR End of previous period],0) then this will give an output of -100, say, if End of Period is blank and End of previous period is 100. Which is what I want

However, as soon as i put it in the SUMX function as per above, then it just returns blank, which is not the desired outcome.

Why is that and can i fix it. I have tried adding "+0" at various places in the measure to no avail