cancel
Showing results for
Did you mean:
Frequent Visitor

## Need Help Creating a measure

Hi PBI Community

Problem -

I need help creating a measure that will calculate the number of tickets that need to be closed in order bring the cumulative average age of the tickets = to target age of tickets. I've included a .pbix file with some sample data and precalculated measures.

Dataset -

Copy and Paste data is at the end of the message

For example

In the sample data set the Target Average Age = 40.

As can be seen in the table:

When Cumulative Average of Tickets = 40, Cumulative Count of Tickets Greater than  Current Age = 11. Therefore the 11 oldest tickets must be closed in order to bring the cumulative average to the target.

While I have been able to figure this out using a table, I haven't been able to translate the table into a single measure. in the actual dataset table 1 will include other details about tickets such as location, status etc and the measure must dynamic and affected by those other filters.

If possible I would also like a measure that would return the corresponding AgeList (Days) value, in this case 79, however I could probably figure that out if someone helps me with the first measure.

Quick Thank you to @Ross73312 for help provided earlier with Cumulative Count measures.

Table 1

Data

Ticket IDAge (Days)

 103JG8MG9JQ 54 103EE5WFU3D 58 1030C25NFD9 8 103LJZEVZX5 17 1037HWX3A7R 30 1032FT62VHN 51 103NMQFBG1A 75 1039KNPJ0B6 72 103VIKYILU2 88 103GPH7Q55O 84 103BNDGYGOK 69 103XVAQ71Y6 66 103IS7Z5MI2 44 1034Q48E6SY 21 103ZY1HMRCL 42 103KWY0UCMH 22 1036TV9TW63 67 103R1II17GZ 75 103DZFSAS0V 74 1038WC18CAI 40 103U49AHXTE 33 103F26JPI40 78 103103TX2EW 18 103W702WDXJ 43 103H5XB4Y8F 90 10333UUDJRB 55 103OAR3B31X 27 103A8OCKOLT 14 10356LMS8VG 32 103RDHV0TFC 81 103CBE4Z4P8 41 103Y9BD7P9U 10 1030NR4A3PL 58 103MKOE9EZH 28 1037ILNHYI4 55 103TQIWPJT0 78 103OOF5O4CM 31 1039LCEWOMI 73 103VT9O596E 62 103GR56DKG1 69 103BO2GC4QX 54 103XWZPKPAJ 32 103JUWYSAKF 32 1034ST7RU4B 2 103QZQHZFEY 12 103LXNQ8QYU 20 10364KZGB8G 15 103S2H8FVSC 32 103D0EING28 59 10387BRV0LU 42 103U58A4LWQ 60 103F35J36FD 20 1031ASSBHP9 72 103N8P1J195 0 103I6MAIMJR 28 1033DJKQ73N 30 103PBGTZRDA 73 103A9D27CN6 0 1035G9B6N7S 60 103RE6LE8HO 78 103CC33MS1K 63 103YK0DLDB7 13 103JHXMTXV3 46 103FFUV2I5P 4 103TR7V374C 24 103OP45BSEZ 68 103AW1EKCYV 61 103VUYNIN8R 59 103HSUWR8SD 18 1032ZR6ZT29 16 103XXOFYDMW 34 103JVLO6YWS 98 10342IXEIGO 34 103Q0FGNTQA 34 103LYCPLE06 10 103759ZUZKT 23 103S3682JUP 64 103E13H14EL 76 103Z90Q9PO7 21 103U6XZH073 39 103GEU9QKIQ 26 1031CQIO51M 99 103N9NRXPBI 35 103IHK05AV4 3 1034FHJDV50 14 103PDESCFPN 85 103BK22KQZJ 22 103WIYBTBJF 2 103RGVKSWT1 82 103DNST0GDX 8 103YLP381NJ 57 103KJMCGLXF 35 103FQJLFXH2 95 1030OGUOHRY 75 103MMDDW2AU 32 1038TAMVMLG 79 1031P5U152B 88 103MX239QM7 35 1038UZCIAWT 75 103TSWLQV6P 97

Measures

```Cumulative Average of Tickets Greater Than Current Age = VAR currentAge = max(Table2[AgeList (Days)])
RETURN
CALCULATE(
AVERAGE(Table1[Age (Days)]),
ALLSELECTED(Table1),
'Table1'[Age (Days)] > currentAge
)```
```Cumulative Average of Tickets Less Than Current Age = VAR currentAge = max(Table2[AgeList (Days)])
RETURN
CALCULATE(
average(Table1[Age (Days)]),
ALLSELECTED(Table1),
'Table1'[Age (Days)] < currentAge
)```
```Cumulative Count of Tickets Greater Than Current Age = VAR currentAge = max(Table2[AgeList (Days)])
RETURN
CALCULATE(
COUNTROWS(Table1),
ALLSELECTED(Table1),
'Table1'[Age (Days)] > currentAge
)```
```Cumulative Count of Tickets Less Than Current Age = VAR currentAge = max(Table2[AgeList (Days)])
RETURN
CALCULATE(
COUNTROWS(Table1),
ALLSELECTED(Table1),
'Table1'[Age (Days)] < currentAge
)```
`Target Age = 40`

Table 2

AgeList (Days)

 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft

## Re: Need Help Creating a measure

Hi @tnrahim,

You want the table visual to display only one record where Cumulative Average of Tickets Less Than Current Age meets the target average age, right? Also, you want a card visual to show corresponding AgeList (Days) value, right?

If so, please first modify the formula for measure [Cumulative Average of Tickets Less Than Current Age] as below:

```Cumulative Average of Tickets Less Than Current Age =
ROUND (
CALCULATE (
AVERAGE ( Table1[Age (Days)] ),
FILTER (
ALL ( Table1[Age (Days)] ),
'Table1'[Age (Days)] < MAX ( Table2[AgeList (Days)] )
)
),
0
)
```

Then, create extra measures:

```Measure1 = IF([Cumulative Average of Tickets Less Than Current Age]=40,1,0)

Measure2 = CALCULATE(MAX(Table2[AgeList (Days)]),FILTER(ALL(Table2),[Measure1]=1))```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Frequent Visitor

bump

Highlighted
Microsoft

## Re: Need Help Creating a measure

Hi @tnrahim,

You want the table visual to display only one record where Cumulative Average of Tickets Less Than Current Age meets the target average age, right? Also, you want a card visual to show corresponding AgeList (Days) value, right?

If so, please first modify the formula for measure [Cumulative Average of Tickets Less Than Current Age] as below:

```Cumulative Average of Tickets Less Than Current Age =
ROUND (
CALCULATE (
AVERAGE ( Table1[Age (Days)] ),
FILTER (
ALL ( Table1[Age (Days)] ),
'Table1'[Age (Days)] < MAX ( Table2[AgeList (Days)] )
)
),
0
)
```

Then, create extra measures:

```Measure1 = IF([Cumulative Average of Tickets Less Than Current Age]=40,1,0)

Measure2 = CALCULATE(MAX(Table2[AgeList (Days)]),FILTER(ALL(Table2),[Measure1]=1))```

Best regards,

Yuliana Gu

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

## Re: Need Help Creating a measure

Thank you @v-yulgu-msft

```Measure4 =
VAR m = [Measure2]
Return
CALCULATE(COUNTROWS(Table1),Filter(Table1,Table1[Age (Days)]>m))```

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors