Need the count of category

DAX DAX Calculations
1

Need the count of category please help. Sample file attached

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

SALES DATA.pbix (1.7 MB)

2

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.

A possible workout, see also PBIX attached :
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)
    image

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

    Summary sales v2 = 
            ADDCOLUMNS( 
                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” !

    image

    image

    image

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

portal.enterprisedna.co

Advanced dynamic grouping modelling and formula techniques

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

portal.enterprisedna.co

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
3

@deltaselect ,

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

Thanks!

  • Brian
2 Likes
4

Thank you so much for that detailed answer @deltaselect! :slight_smile:

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.

5

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.

6

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.