Skip to main content
cancel
Showing results for 
Search instead 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

Reply
garfieldhe
Employee
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
v-xuding-msft
Community Support
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])

 

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

 

 

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

Please @mention me in your reply if you want a response.

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.
If you found this post helpful, please give Kudos C

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

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

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?

Please @mention me in your reply if you want a response.

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.
If you found this post helpful, please give Kudos C

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

Hi Allison,

 

This is what I trying to show

 

WeChat Screenshot_20200816180827.png

 

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!

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

Please @mention me in your reply if you want a response.

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.
If you found this post helpful, please give Kudos C

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

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

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)


Please @mention me in your reply if you want a response.

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.
If you found this post helpful, please give Kudos C

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

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.

 

WeChat Screenshot_20200816180827.png

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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