MY COUNTROWS DOESN"T SHOW TOTAL ROWS IN A TABLE

[garfieldhe]

I need to find out the number of customers who latest active month == current month (selected)

So, here is my DAX, try to find out some one who active in current month && also has latest month == current month, my DAX can identify who are they now, but it won't show the total in the table

Lost customers =

VAR currentMonth = FIRSTDATE(KV_Cogsvc_Main[Month Year])

VAR latestRecord = CALCULATE(MAX(KV_Cogsvc_Main[Month Year]), ALLEXCEPT(KV_Cogsvc_Main, KV_Cogsvc_Main[Customer Name]))

VAR lostCustomersa = CALCULATETABLE(

VALUES(KV_Cogsvc_Main[Customer Name]),

FILTER(KV_Cogsvc_Main, KV_Cogsvc_Main[Month Year]=latestRecord))

VAR lostCustomersb = CALCULATETABLE(

VALUES(KV_Cogsvc_Main[Customer Name]),

FILTER(KV_Cogsvc_Main, KV_Cogsvc_Main[Month Year]=currentMonth))

VAR lostCustomersc = NATURALINNERJOIN(lostCustomersb, lostCustomersa)

RETURN COUNTROWS(lostCustomersc)

[v-xuding-msft]

Hi @garfieldhe ,

Have you resolved it? If not, you could use SUMX() to have a try.

https://docs.microsoft.com/en-us/dax/sumx-function-dax

Measure = SUMX('your table',[Lost customers])

https://exceleratorbi.com.au/use-sum-vs-sumx/ 

https://radacad.com/sum-vs-sumx-what-is-the-difference-of-the-two-dax-functions-in-power-bi

[AllisonKennedy]

Variables are calculated in the context when they are defined, not when they're used, so unless you have this measure in a table with customer name, this won't work. You need to define your latest date within the context of a chosen customer.

Also, I think INTERSECT will work better here.

See below sample measure (note I haven't tested so may need some syntax fixing):

Lost customers =

VAR lostCustomersa = CALCULATETABLE(
VALUES(KV_Cogsvc_Main[Customer Name]),



VAR latestRecord = CALCULATE(MAX(KV_Cogsvc_Main[Month Year]), ALLEXCEPT(KV_Cogsvc_Main, KV_Cogsvc_Main[Customer Name]))
RETURN

FILTER(KV_Cogsvc_Main, KV_Cogsvc_Main[Month Year]=latestRecord))


VAR lostCustomersb = CALCULATETABLE(
VALUES(KV_Cogsvc_Main[Customer Name]),
VAR currentMonth = FIRSTDATE(KV_Cogsvc_Main[Month Year])
RETURN
FILTER(KV_Cogsvc_Main, KV_Cogsvc_Main[Month Year]=currentMonth))


VAR lostCustomersc = INTERSECT(lostCustomersb, lostCustomersa)


RETURN COUNTROWS(lostCustomersc)

[garfieldhe]

Thank you for the reply Allison, but the result still the same, the customers who met the requirements has been found, but the total number of them are not sum up in the table by month...

[AllisonKennedy]

Can you please provide more info on how you are trying to use this measure and what is working and what isn't with screenshots or sample file with confidential info removed? How do you know the customers who met the requirements have been found? What is displayed instead of total?

[garfieldhe]

Hi Allison,

This is what I trying to show

and here is the Formula I used for calculate the new customers number every month

New customers =

VAR currentCustomers = VALUES(KV_Cogsvc_Main[Customer Name])

VAR currentMonth = FIRSTDATE(KV_Cogsvc_Main[Month Year])

VAR pastCustomers = CALCULATETABLE(VALUES(KV_Cogsvc_Main[Customer Name]),

ALL(KV_Cogsvc_Main[RecordDate].[月份],KV_Cogsvc_Main[RecordDate].[MonthNo],KV_Cogsvc_Main[RecordDate].[年])

, KV_Cogsvc_Main[Month Year]<currentMonth)

VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)

RETURN COUNTROWS(newCustomers)

Thanks in advance!

[AllisonKennedy]

Thanks, this helps a bit. What formula are you currently using for Lost Customers? Not sure why you have -1 for COUNTROWS function?

[garfieldhe]

Hi Allison,

it is the formula you shared before, I just *-1 in COUNTROWS

Lost customers =

VAR lostCustomersa = CALCULATETABLE(

VALUES(KV_Cogsvc_Main[Customer Name]),

VAR latestRecord = CALCULATE(MAX(KV_Cogsvc_Main[Month Year]), ALLEXCEPT(KV_Cogsvc_Main, KV_Cogsvc_Main[Customer Name]))

RETURN

FILTER(KV_Cogsvc_Main, KV_Cogsvc_Main[Month Year]=latestRecord))

VAR lostCustomersb = CALCULATETABLE(

VALUES(KV_Cogsvc_Main[Customer Name]),

VAR currentMonth = FIRSTDATE(KV_Cogsvc_Main[Month Year])

RETURN

FILTER(KV_Cogsvc_Main, KV_Cogsvc_Main[Month Year]=currentMonth))

VAR lostCustomersc = INTERSECT(lostCustomersb, lostCustomersa)

RETURN COUNTROWS(lostCustomersc) * -1

[AllisonKennedy]

Phew! I thought something strange was going on with the -1 for a second. 😛

Okay, so what's happening is that you are defining a 'Lost Customer' as someone who has the current month and last order date month as the same value. When you get to the total row, the current month value is no longer a single value.

You have currently used:
VAR currentMonth = FIRSTDATE(KV_Cogsvc_Main[Month Year])
to define your current month value, (I would prefer we use a date table ultimately, but won't matter for now).

In the total row, that is the first date that ANY customer ordered anything in the Main table. Adding a SUMX to your expression should work. I am assuming you want to do a count month by month, then sum those totals?

See if this works:
Lost customers =

VAR lostCustomersa = CALCULATETABLE(
VALUES(KV_Cogsvc_Main[Customer Name]),
VAR latestRecord = CALCULATE(MAX(KV_Cogsvc_Main[Month Year]), ALLEXCEPT(KV_Cogsvc_Main, KV_Cogsvc_Main[Customer Name]))
RETURN
FILTER(KV_Cogsvc_Main, KV_Cogsvc_Main[Month Year]=latestRecord))

VAR lostCustomersb = CALCULATETABLE(
VALUES(KV_Cogsvc_Main[Customer Name]),
VAR currentMonth = FIRSTDATE(KV_Cogsvc_Main[Month Year])
RETURN
FILTER(KV_Cogsvc_Main, KV_Cogsvc_Main[Month Year]=currentMonth))

VAR lostCustomersc = INTERSECT(lostCustomersb, lostCustomersa)

RETURN SUMX(VALUES(KV_Cogsvc_Main[Month Year]), COUNTROWS(lostCustomersc) * -1)

[garfieldhe]

thank you Allison, I have the same understanding, but somehow the lost customer column still not able to sum up the number of rows by month

I applied your latest solution, but the result is the same, I am selecting 2020-07, so total customers is 213, there are 31 customers who join on 2020-07, and some latest month won't last till latest 2020-08, but the number of lost customers still not able to be summed in the table.

[Greg_Deckler]

@garfieldhe - This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907