# Need the count of category

How many “S CLASS”
How many “A CLASS” and so on…

SALES DATA.pbix (1.7 MB)

Hi @khurram ,

In order to obtain the correct count of the classes, a classification support table has to be added and the data needs to be summarized, as the sales data is too detailed.

EDNA SALES DATA counting v2.pbix (1.5 MB)

1. Create / add a classification Support table “Customer Class”, with minimum and maximum Sales quantity per month per class (sequence included for sorting) ( no connection possible in the data model)

2. Create a calculated table with summarized sales quantity data per customer per month

``````Summary sales v2 =
SUMMARIZE( 'SALES DATA', 'SALES DATA'[CUSTOMER ], 'Sales Data'[YEAR], 'Sales Data'[MONTH NO], 'Sales Data'[MONTH]),
"Monthly Sales Qty", [SALES QTY], "Nr Month", CONCATENATE( 'Sales Data'[MONTH NO] & " ",'Sales Data'[MONTH]))
``````
• Note 1: In this case is a calculated summarized table necessary in my opinion. A counting measure for the classes in the “Sales data table” would give incorrect figures, as the Counta-formula on a text column will count all lines (68.791) instead of the 1.169 classes to be reported; the granularity of the “Sales data table” is too high.

• Note 2 : In this calculated table Month Number and Month and Month are concatenated.

1. Create two base measures, based upon the newly created calculated table, counting the Classes and the Quantity Sold

Base Qty Class (summT) = COUNTA( ‘Summary sales2’[CUSTOMER ])

Base Qty Sold (summT) = SUM( ‘Summary sales2’[Monthly Sales Qty])

2. Create two measures which calculates resp. the quantity of classes, and the quantity of sales
Both are based upon the calculated table, and uses the base measures above.
With those measures the grouping in classes is made as defined in the support table “Customer Class”, for both the count of the classes and the quantity sold

``````Qty Class =
CALCULATE( [Base Qty Class (summT)],
FILTER( 'Summary sales v2',
COUNTROWS(
FILTER( 'Customer Class',
[Base Qty Sold (summT)] >=  'Customer Class'[Min Qty] &&
[Base Qty Sold (summT)] <   'Customer Class'[Max Qty] ) ) > 0 ) )
``````
``````Qty Sold =
CALCULATE( [Base Qty Sold (summT)],
FILTER( 'Summary sales v2',
COUNTROWS(
FILTER( 'Customer Class',
[Base Qty Sold (summT)] >=  'Customer Class'[Min Qty] &&
[Base Qty Sold (summT)] <   'Customer Class'[Max Qty] ) ) > 0 ) )
``````
3. Create an average sales quantity per class measure by measure branching

Avg Qty per Class = DIVIDE([Qty Sold], [Qty Class], 0)

4. Reports can be made, see some example reports below. (drill down/up in the reports)
Be aware that the reports are based upon the calculated table “Summary sales v2” !

5. The grouping principle with a support table is explained in two videos by Sam McKay for Accounts Receivable reporting, see :

### Advanced dynamic grouping modelling and formula techniques

Create comprehensive financial reports in Power BI that represent key insights in a compelling and dynamic way

### Visualization options

Create comprehensive financial reports in Power BI that represent key insights in a compelling and dynamic way

If you have further questions or remarks, please let me know.

Kind regards, JW

3 Likes

Epic response. I’ve bookmarked this one for my own future reference.

Thanks!

• Brian
2 Likes

Thank you so much for that detailed answer @deltaselect!

Hello @khurram, did the response provided by @deltaselect help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

Hi @khurram, we’ve noticed that no response has been received from you since August 29.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hi @khurram, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.