Frequency of counts

[Anonymous]

Premise: I am quite new in Power BI and DAX. So be kind with my question 🙂

I have a list of orders like this (my database is about 50K rows):

(Order Table)

Order

A

B

B

C

B

D

E

D

F

F

Each line is a row of the same order.

Firsly, I would count the orders so I can have the nr. of rows.

(Orders Count)

Orders	Nr. Rows
A	1
B	3
C	1
D	2
E	1
F	2

Then, I would summerize how many orders I have with 1 row, 2 rows, 3 rows etc. like below:

Nr. Rows	Nr. Orders	% N. ordini
1	3	50%
2	2	33%
3	1	17%

Someone can help me!? (maybe the topic has already been dealt with, but I could not find it)

Thanks

[AlB · Accepted solution]

Hi @Anonymous 

See it all at work in the attached file.

A) The first requirement is simple. Place Table1[Order] in the rows of a matrix visual and then create this measure ad place it in values of the matrix:

CountMeasure = COUNT(Table1[Order])

B) The second one is a bit trickier.

1. Create an additional one-column table that we will use in rows of the visual. The SELECTCOLUMNS is just to change the name of the column to "Nr. Rows"

AuxTable =
SELECTCOLUMNS (
    GENERATESERIES (
        1;
        MAXX (
            ADDCOLUMNS ( Table1; "_Count"; CALCULATE ( COUNT ( Table1[Order] ) ) );
            [_Count]
        )
    );
    "Nr. Rows"; [Value]
)

2. Place AuxTable[Nr. Rows] in the rows of a matrix visual

3. Create this measure and place it in values of the matrix:

Nr. Orders =
COUNTROWS (
    FILTER (
        ADDCOLUMNS ( ALL ( Table1[Order] ); "Res"; [CountMeasure] );
        [Res] = SELECTEDVALUE ( AuxTable[Nr. Rows] )
    )
)

4. Create this other measure that uses the previous one and place it in values of the matrix:

% N. ordini =
VAR _Total =
    SUMX ( ADDCOLUMNS ( ALL ( AuxTable[Nr. Rows] ); "Res"; [Nr. Orders] ); [Res] )
RETURN
    DIVIDE ( [Nr. Orders]; _Total )

[Anonymous]

It works!

Thanks!

And if I want to set classes for the highest values? 

1

2

3

4-10

11-20

I set the Aux value as follow

Min nr. Row	Max nr. Row	Class Name
1	1	1
2	2	2
3	3	3
4	10	4-10
11	20	11-20

I modify your code with:

Nr. Orders = 
COUNTROWS
(FILTER
(ADDCOLUMNS(ALL(Table1[Order]);"Res";[CountMeasure]);
[Res]>=SELECTEDVALUE(AuxTable[Min Nr.Row])&&[Res]<=SELECTEDVALUE(AuxTable[Max Nr.Row])))

And it works!

The problem is in the %Nr. Orders that calculate a % that is not correct.

Any clue?

[AlB]

@Anonymous 

Try this, almost the same, where [Nr. Orders] is the one you just defined anew:

% N. ordini 2 =
VAR _Total =
    SUMX ( ADDCOLUMNS ( ALL ( AuxTable ); "Res"; [Nr. Orders] ); [Res] )
RETURN
    DIVIDE ( [Nr. Orders]; _Total )

@HotChilli 

Awesome. Very smart solution. And substantially more efficient than mine.  

[HotChilli]

AIB, respect to you for your contribution to this site.  I wanted to show a slightly different approach for anyone looking at this.

I created a summary table

OrderSummary = SUMMARIZECOLUMNS(   Table1[Order], "OrderCount",  [CountMeasure])

Then, using just the OrderCount field, I pulled it on to a visualisation(table) 3 times.

The first one - set to 'Don't Summarize' in the field well.

The second one - set to Count in the field well.

The third one - set to Count in the field well AND also select 'Show value as' and choose 'Percent of grand total'.