cancel
Showing results for
Did you mean:
Regular Visitor

## count of grouped information

We are trying to achieve something like below.

Based on the sum of MissedInterval for a meter id, we need to group the meter into a band and then the count of meters falling into each band range defined. The challenge is when changing the slicer/filter.

For ex: 1) when selecting a range of 01-Jan to 10-Jan, meter ID=1 should fall into band of “1-3 Reads” as sum of missed interval=3.

2)  If selected a range of 01-Jan to 15-Jan, meter ID=1 should fall into band of “10+ Reads” as sum of missed interval=11, similar for rest of the meters.

Dummy input for scenario explained

 Meter Info table MeterUniqueNumber IntervalsCollected MissedIntervals IntervalDate Band 1 12 3 03-01-2018 1 7 8 15-01-2018 1 10 5 27-01-2018 1 14 1 28-01-2018 2 13 2 15-01-2018 2 15 0 27-01-2018 Band info table Min Max Band -5 0 No Missing 0 1 1 Read 2 10 1-10 Read 11 10000 10+ Read

Expected output:

 For Scenario1( 01-Jan to 10 Jan) For Scenario2( 01-Jan to 15 Jan) Band No of meters Band No of meters No Missing 0 No Missing 0 1 Read 0 1 Read 0 1-10 Read 1 1-10 Read 1 10+ Read 0 10+ Read 1

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator

## Re: count of grouped information

You can create a measure below:

No of meters = IF(SUM('Meter Info table'[MissedIntervals])>=MAX('Band info table'[Min])&& SUM('Meter Info table'[MissedIntervals])<=MAX('Band info table'[Max]),1,0)

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Moderator

## Re: count of grouped information

You can create a measure below:

No of meters = IF(SUM('Meter Info table'[MissedIntervals])>=MAX('Band info table'[Min])&& SUM('Meter Info table'[MissedIntervals])<=MAX('Band info table'[Max]),1,0)

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.