DAX Measure for New Customer Sales Incorrectly Aggregating in Grand Total

SasoosaS — Mon, 31 Jul 2023 20:37:38 GMT

Hello Power BI Community,

I am experiencing a problem with a DAX measure that calculates new customer sales. The measure works correctly at the row level of my visuals but it's not returning the expected value in the total line.

Here's the DAX code I'm using:

New Customer Sales Value =

VAR CustomerTM = VALUES(Sales[partner_count_code])

VAR PriorCustomers = CALCULATETABLE(

VALUES(Sales[partner_count_code]),

FILTER(

ALL(DateTable),

DateTable[Date] > MIN(DateTable[Date]) - churnTimeFrame[churnTimeFrame Value] &&

DateTable[Date] < MIN(DateTable[Date])

)

VAR NewCustomers = EXCEPT(CustomerTM, PriorCustomers)

RETURN

CALCULATE(

SUM(Sales[total_selling_price]),

FILTER(

Sales,

Sales[partner_count_code] IN NewCustomers

)

In the PriorCustomers variable, I'm using MIN(DateTable[Date]) to find the earliest date, but I suspect this might be causing the issue when it comes to calculating the grand total. It appears that this function returns the earliest date in the entire DateTable in the context of the total row, rather than the earliest date in the context of each row.

I have also created my DateTable using DAX with the following code:

DateTable =

ADDCOLUMNS (

CALENDAR(MINX(Sales, sales[doc_date]),MAXX(Sales, sales[doc_date])),

"DateAsInteger", FORMAT ( [date], "YYYYMMDD" ),

"Year", YEAR ( [date] ), "MonthNo", FORMAT ( [date], "MM" ),

"YearMonthNo", FORMAT ( [date], "YYYY/MM" ),

"YearMonth", FORMAT ( [date], "YYYY/mmm" ),

"MonthShort", FORMAT ( [date], "mmm" ),

"MonthLong", FORMAT ( [date], "mmmm" ),

"WeekNo", WEEKDAY ( [date] ),

"WeekDay", FORMAT ( [date], "dddd" ),

"WeekDayShort", FORMAT ( [date], "ddd" ),

"Quarter", "Q" & FORMAT ( [date], "Q" ),

"YearQuarter", FORMAT ( [date], "YYYY" ) & "/Q" & FORMAT ( [date], "Q" ))

My sales data is related to the DateTable with a many-to-one relationship (sales.doc_date > DateTable.date). My sales table contains a field partner_count_code that identifies each customer, and total_selling_price to represent the sales amount.

When I create a visual that includes the New Customer Sales Value measure, the total row seems to be aggregating the entire total sales, not just the sales for new customers. I've even manually checked and found that even when filters are applied, the total is still incorrect.

Could anyone advise me on how to adjust my DAX formula to calculate the correct total for new customer sales regardless of the filter context?

Re: DAX Measure for New Customer Sales Incorrectly Aggregating in Grand Total

Anonymous — Tue, 01 Aug 2023 02:17:42 GMT

Hi SasoosaS ,

You can create a new measure to place on the visual object.

_New Customer Sales Value = SUMX(VALUES('DateTable'[YearMonth]),[New Customer Sales Value])

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.

Re: DAX Measure for New Customer Sales Incorrectly Aggregating in Grand Total

SasoosaS — Tue, 01 Aug 2023 08:06:48 GMT

Hi Anonymous,

Thank you for your solution.

The new measure you suggested works perfectly and has resolved my issue.

_New Customer Sales Value = SUMX(VALUES('DateTable'[YearMonth]),[New Customer Sales Value])

Your help is greatly appreciated. Thank you!

Best Regards,

Sašo

topic Re: DAX Measure for New Customer Sales Incorrectly Aggregating in Grand Total in DAX Commands and Tips

DAX Measure for New Customer Sales Incorrectly Aggregating in Grand Total

Re: DAX Measure for New Customer Sales Incorrectly Aggregating in Grand Total

Re: DAX Measure for New Customer Sales Incorrectly Aggregating in Grand Total