cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
McLace
Frequent Visitor

Frequency of counts

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)

OrdersNr. Rows
A1
B3
C1
D2
E1
F2

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

Nr. RowsNr. Orders% N. ordini
1350%
2233%
3117%

 

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

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Frequency of counts

Hi @McLace 

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 )

 

 

View solution in original post

4 REPLIES 4
Super User III
Super User III

Re: Frequency of counts

Hi @McLace 

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 )

 

 

View solution in original post

Super User I
Super User I

Re: Frequency of counts

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'.

McLace
Frequent Visitor

Re: Frequency of counts

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. RowMax nr. RowClass Name
111
222
333
4104-10
112011-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?
Super User III
Super User III

Re: Frequency of counts

@McLace 

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.  

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors