Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 )
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 )
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])))
@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 )
Awesome. Very smart solution. And substantially more efficient than mine.
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'.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |