cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Employee

## MY COUNTROWS DOESN"T SHOW TOTAL ROWS IN A TABLE

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)
10 REPLIES 10
Community Support

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])``

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
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)

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Employee

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...

Super User
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?

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Employee

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)

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

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Employee

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
Super User
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)

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Employee

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.

Super User

@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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors