cancel
Showing results for
Did you mean:
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)

 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

1 ACCEPTED SOLUTION

Accepted Solutions 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 )```

4 REPLIES 4 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 )``` 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'.

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. Row Max nr. Row Class Name 1 1 1 2 2 2 3 3 3 4 10 4-10 11 20 11-20

`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

## Re: Frequency of counts

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 )
```

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

Announcements #### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section. #### ‘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
Users online (1,358)