Excel dax count rows that evaluate between values

Question

I'm kinda new to dax and wondering if someone could help me with this:

I need a measure that count the number of rows that meets a specific criteria , and where the result of dividing each row for column A with column B falls in a specific range. Here is an example how I would have solved it in Excel:

+-----+-----+-------------------+
|  A  |  B  | Calculated column |
+-----+-----+-------------------+
| 1,4 | 1,3 | 108 %             |
| 1,1 | 1,4 | 79 %              |
| 0,8 | 1,1 | 73 %              |
|   1 | 1,3 | 77 %              |
| 1,3 | 1,4 | 93 %              |
| 1,1 |   1 | 110 %             |
| 1,3 | 1,3 | 100 %             |
| 1,5 | 0,8 | 188 %             |
| 0,9 | 1,4 | 64 %              |
| 1,1 |   1 | 110 %             |
+-----+-----+-------------------+

Result: 5

Formula in Excel:=COUNTIFS(E8:E17;">=0,9";E8:E17;"<=1,1")

I could do the same in powerpivot: Create a calculated column that divides column A with column B, and then count the number of rows in that column that falls within the specific range, but I will have quite a few ranges and would like to avoid all those extra columns if possible.

Is it possible to calculate this in one measure?

BR Johan

RADO · Accepted Answer · 2018-07-02 06:32:14Z

0

Let's say your table name is "Fact".

Measure:

   Result     =
        COUNTROWS ( 
          FILTER ( 'Fact',
            VAR Ratio = DIVIDE ( 'Fact'[A], 'Fact'[B] ) 
            RETURN Ratio >= 0.9 && Ratio <= 1.1 ))

How it works: First, inside FILTER function A/B ratio is calculated and stored in a variable. Then table is filtered by the ratio within the range you need. Finally, rows of the filtered table are counted.

edited Jul 2, 2018 at 6:32

answered Jun 29, 2018 at 22:41

RADO

8,1873 gold badges26 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jtm45 Over a year ago

That works like a charm! I guess it was the VAR Ratio bit that had me stumped. Thanks!

Collectives™ on Stack Overflow

Excel dax count rows that evaluate between values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related