cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
praiselyabraham Regular Visitor
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 v-qiuyu-msft
Moderator

Re: count of grouped information

Hi @praiselyabraham,

 

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)

 

e1.PNGe2.PNG

 

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.
1 REPLY 1
Highlighted
Moderator v-qiuyu-msft
Moderator

Re: count of grouped information

Hi @praiselyabraham,

 

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)

 

e1.PNGe2.PNG

 

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.