cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kattlees
Post Patron
Post Patron

Count of Customers with sales by day

I have a table of V_ACCT_CHARGES with 

AcctNum, ChgServDate, ChgAmt

123456       2/4/2018         934.28

123456       2/5/2018         934.28

123456       2/4/2018         -934.28

654321        2/4/2018         934.28

 

I need to make a bar graph that shows the total number of accts with more than a 0 chgamt total by day

 

So there would be 1 on both 2/4/2018 and 2/5/2018  Because account 123456 has a total charge of 0 on 2/4/2018 it won't count that person. 

 

I have a measure created for total charges that totals the charges correctly and I get

2/4/2018     934.28

2/5/2018     934.28

 

But when I try to pull it into a graph, it counts 2 for 2/4/2018.

My Axis I have ChgServDate
My Value I have Count of AcctNum
Filter I have Total Charge > 0

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @kattlees

You could create a new measure then add this measure to the Value field.

Measure = CALCULATE(SUM(Sheet5[ChgAmt]),ALLEXCEPT(Sheet5,Sheet5[ChgServDate],Sheet5[AcctNum]))

Measure 3 = CALCULATE(DISTINCTCOUNT(Sheet5[AcctNum]),FILTER(ALLEXCEPT(Sheet5,Sheet5[ChgServDate]),[Measure]>0))

4.png

 

 

Best Regards

maggie

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @kattlees

You could create a new measure then add this measure to the Value field.

Measure = CALCULATE(SUM(Sheet5[ChgAmt]),ALLEXCEPT(Sheet5,Sheet5[ChgServDate],Sheet5[AcctNum]))

Measure 3 = CALCULATE(DISTINCTCOUNT(Sheet5[AcctNum]),FILTER(ALLEXCEPT(Sheet5,Sheet5[ChgServDate]),[Measure]>0))

4.png

 

 

Best Regards

maggie

View solution in original post

THIS WORKING GREAT IF WE SELECT THE SPECIFIC MONTH ,BUT WHAT IF WANT AOUTMATICALLY DISPLAY MAX MONTH OF THE YEAR @v-juanli-msft 

Thank you. I had found a way to do it yesterday but was twice as many steps. I like your solution much better!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.