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.
Hi,
So I want to count the number of rows in a table where the column value in the table is between two ranges.
In Excel it is like =COUNTIFS(A1:A50,">=1", A1:A50,"<=10")
how do I do this in powerbi?
I have played around with CountX, Countrows, filters .... it is currently beyond me.
Thanks for any advice.
John
Solved! Go to Solution.
You can add a column and use the expresion below.
Column = SWITCH( TRUE, Table1[pct] >= 0 && Table1[pct] <= 0.05, "0-5%", Table1[pct] >= 0.06 && Table1[pct] <= 0.1, "6-10%", Table1[pct] >= 0.11 && Table1[pct] <= 0.15, "11-15%", Table1[pct] >= 0.16 && Table1[pct] <= 0.2, "16-20%", Table1[pct] >= 0.2 && Table1[pct] <= 0.25, "20-25%", ">25%" )
Hope this helps
Mariusz
Hi @johnmelbourne
The below expresion should do the trick.
Measure = COUNTROWS( FILTER( YourTable, YourTable[YourField] >= 1 && YourTable[YourField] <= 10 ) )
Hope this helps
Mariusz
Hi @johnmelbourne
Would you mind to explain in more detail what you are looking to achieve?
Many Thanks
Mariusz
Hi,
Thanks for the follow up.
I want to filter this example data set into 5 groups and count those with values 0-5%, 6-10%, 11-15%, 16-20% and >25%, then filter by quarter.
So for example for Sep I would have 5 in the group 6-10%, and in December I would have 1.
The simple way for me to do it that I can think of is create 5 calculated columns and use an if statement with the result of 1 if true, then sum them in a 5 measures, but there is probably a neater solution.
Here is the data.
You can add a column and use the expresion below.
Column = SWITCH( TRUE, Table1[pct] >= 0 && Table1[pct] <= 0.05, "0-5%", Table1[pct] >= 0.06 && Table1[pct] <= 0.1, "6-10%", Table1[pct] >= 0.11 && Table1[pct] <= 0.15, "11-15%", Table1[pct] >= 0.16 && Table1[pct] <= 0.2, "16-20%", Table1[pct] >= 0.2 && Table1[pct] <= 0.25, "20-25%", ">25%" )
Hope this helps
Mariusz
Excellent solution and has broadened my knowledge.
Many thanks.
I had to round my percentage values to 2 digits, otherwise those percentages in between the ranges (say between 5 and 6, eg: 5.1 ended up not being captured and resulted in the catch all else >25 range, and also remove some of the equals to make everything suit my needs.
So ended up with
Column =
SWITCH(
TRUE,
ROUND(MyTable[Pct],2) >= 0 && ROUND(MyTable[Pct],2) <= 0.05, "0-5%",
ROUND(MyTable[Pct],2) > 0.05 && ROUND(MyTable[Pct],2) <= 0.1, "6-10%",
ROUND(MyTable[Pct],2) > 0.10 && ROUND(MyTable[Pct],2) <= 0.15, "11-15%",
ROUND(MyTable[Pct],2) > 0.15 && ROUND(MyTable[Pct],2) <= 0.2, "16-20%",
ROUND(MyTable[Pct],2) > 0.2 && ROUND(MyTable[Pct],2) <= 0.25, "21-25%",
">25%"
)
Regards
John
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 |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |