Count num. of customers with sales grand total >=100

Zaoka

New Member
Joined
Sep 2, 2015
Messages
19
Hi,

Can you help me with the creation of custom measurement that would count number of customers that have Grand total sales greater than 100$

I created this measurement, that count active customers per Sales person and brand if net sales per brand is greater that 0

Code:
=COUNTROWS(FILTER(ADDCOLUMNS(SUMMARIZE(SalesSource;SalesSource[DSDBC]);"zaoka";MAXX(VALUES(SalesSource[Brand]);CALCULATE(SUM([Net sales value $]);ALL(SalesSource[Net sales value $]))));[zaoka]>0))

but i don't know how to add code that would first check sales grand total per customer and if sales is >=100$ then count that customer as active per brand.

This is Example of data

HTML:
https://www.dropbox.com/s/79dh21slo42c1hi/Count%20example.xlsb?dl=0
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Thank you, but it does not work as i imagined it.


let's put it this way


First condition for customer to be counted is to be over 100 in total sales, second condition is for sales per brand to be over 0 .

(on the above dropbox link I set an example where the first condition is 300 for better understanding, sheet manual check).
 
Last edited:
Upvote 0
Using the same measure, if I change the layout of my pivot table:


TArDjwz.png


I tie to your 'manual check' tab:

3JclJWs.png
 
Upvote 0
Which ones are the blue ones? The value inside the pivot table are just the sum of ''Net sales value $' ?

tQ6N4qi.png
 
Upvote 0
(I did re-upload excel example,after your first post sorry for that)
same link in first post
 
Upvote 0
Ok, try using this one:

=<br><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">COUNTROWS</span><span class="Parenthesis" style="color:#969696"> (</span> Customers <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">FILTER</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">VALUES</span><span class="Parenthesis" style="color:#969696"> (</span> Customers[Customer code] <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">SUM</span><span class="Parenthesis" style="color:#969696"> (</span> Sales[Net sales value $] <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent8">        </span><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">ALL</span><span class="Parenthesis" style="color:#969696"> (</span> Products[Brand] <span class="Parenthesis" style="color:#969696">)</span><br><span class="indent8">        </span><span class="Parenthesis" style="color:#969696">)</span> >= <span class="Number" style="color:#EE7F18">300</span><br>            && <span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">SUM</span><span class="Parenthesis" style="color:#969696"> (</span> Sales[Net sales value $] <span class="Parenthesis" style="color:#969696">)</span><br><span class="indent8">        </span><span class="indent4">    </span><span class="Parenthesis" style="color:#969696">)</span> > <span class="Number" style="color:#EE7F18">0</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#969696">)</span><br><span class="Parenthesis" style="color:#969696">)</span><br>
 
Upvote 0
this one work :) thank you sir, i think i understand,

is it possible to do this without these 2 extra tables?
 
Last edited:
Upvote 0
You could but I strongly advise to normalize your data into the 2 dimension tables and one fact table as this is how Power Pivot is supposed to perform best
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top