Count customers with total sum of sales bigger than 0 over a selected time range

[Semih87]

Hi,

I would like to count the distinct count of customers where the total sum of sales is bigger than 0 over a selected time range(it's possible that a sales amount for an order is negative, because of a return). Here the data set structure:

Date	Order ID	Customer ID	Sales
01.01.2020	1	A	50€
01.01.2020	2	B	60€
02.01.2020	1	A	-50€
03.01.2020	3	C	100€
04.01.2020	4	A	30€
05.01.2020	5	B	10€

Let's assume to count the customers for the time range between 02.01.2020 - 04.01.2020 where the total sales of the customers are bigger than 0. The correct answer would be:

Customer A = -50€+30€ = -20€ -> not to count

Customer C = 100€ -> to count

The measure should calculate 1 as the correct answer.

I already have a measure which works properly but I am facing the issue that Power BI Service cannot calculate that measure due to insufficient memory availability:
"You have reached the maximum allowable memory allocation for your tier. Consider upgrading to a tier with more available memory."

Here the underlying measure:

Measure = 
COUNTROWS(
   FILTER(
      ADDCOLUMNS(
           SUMMARIZE(
               FACT_TABLE,
               FACT_TABLE[CUSTOMER_ID]
           )
           ,"SumSales",CALCULATE(SUM(FACT_TABLE[Sales]))
      )
      ,[SumSales]>0
   )
)

Then I tried to use a direct connection to solve this problem, but then I have got the next error message:

Does anyone have an idea of how to change the measure so that it should work properly?

Best regards

[westwrightj]

Another option similar to the one by amitchandak is to use variables - Some folks like to be very procedurial so this would work as well

In the dax measure below you have summarized the table so it is a row per customer with their total sales. In the second variable we filter that table to just rows where total sales is greater than 0

In the actual measurement we count the rows in the second variable.

While this might be a clunky approach, if you start to build out very complex measures this might help you keep track of things nicely.

Number of Customer Greater Than 0 = 

/*Create a summary table */
var Summary_Table = SUMMARIZE('Test Table', 'Test Table'[CustomerID], "Total Sales", SUM('Test Table'[Sales]))
var Summary_Table_GreaterThan0 = FILTER(Summary_Table, [Total Sales] > 0)

return

/*Count the number of rows in the table*/

CALCULATE(COUNTROWS(Summary_Table_GreaterThan0))

[Semih87]

Hi @westwrightj ,

thank you for your reply. I agree that the use of variables helps a lot to read the measures better and in some cases, it can improve the performance as well. But in my case, there is no performance boost by using variables. 

Best regards

[v-deddai1-msft · Accepted solution]

Hi @Semih87 ,

You need to upgrade the shared capacity with premiumn capacity to solve this problem, because you have used resources that exceed the upper limit, or you need to optimize the underlying data source to make the query return less data.

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

Best Regards,

Dedmon Dai

[Semih87]

You are right, the issue is that one of the DAX functions use too much memory capacity. In the end, I changed the underlying data so that the calculation can be done in an efficient way.

[amitchandak]

@Semih87 , Try like this

Measure = 
COUNTROWS(
   FILTER(
           SUMMARIZE(
               FACT_TABLE,
               FACT_TABLE[CUSTOMER_ID]
           ,"SumSales",CALCULATE(SUM(FACT_TABLE[Sales]))
      )
      ,[SumSales]>0
   )
)

is it a direct query. There is a limit on the base data query limit.

[BINoob0815]

Hello amitchandak,

I have a similar challenge. I would like to know how many customers have had or exceeded revenue of 2500 € in the past 12 months. I have created a measure to first even sum up the revenues the last 12 months:

Rev. Last 12 Months =

VAR MaxDate = MAX(Datumstabelle[Date])

VAR MinDate = EDATE( MaxDate , -12 )

VAR Result =

CALCULATE(

    [Act. Sales YTD],

    FILTER( ALL(Datumstabelle[Date]),

    Datumstabelle[Date] <= MaxDate &&

    Datumstabelle[Date] > MinDate )

)

RETURN

Result

Is there a way to now identifiy the number of customers that have had >=2500 € last 12 months? Or do I need to add a column in the table itself? Thanks a ton.

Cheers

[Semih87]

Hi @amitchandak ,

thank you for your reply. I tried your approach and get another (wrong) result - actually it should be the same. However, the use of SUMMARIZE only is not recommended for performance reasons. Please see the blog entry of  @marcorusso  https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/. He recommends always to favor the ADDCOLUMNS version.

Best regards