cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tnrahim
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 - 

Download From Google Drive

https://drive.google.com/file/d/1AR1wtoSw0iqdi0tfiYua2fD_PLEJlTnm/view?usp=sharing

 

Copy and Paste data is at the end of the message

 

Additional Details -

 

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.

 

 

Capture.PNG

 

 

 

 

 

 

 

 

 

 

 

Table 1 

Data

Ticket IDAge (Days)

103JG8MG9JQ54
103EE5WFU3D58
1030C25NFD98
103LJZEVZX517
1037HWX3A7R30
1032FT62VHN51
103NMQFBG1A75
1039KNPJ0B672
103VIKYILU288
103GPH7Q55O84
103BNDGYGOK69
103XVAQ71Y666
103IS7Z5MI244
1034Q48E6SY21
103ZY1HMRCL42
103KWY0UCMH22
1036TV9TW6367
103R1II17GZ75
103DZFSAS0V74
1038WC18CAI40
103U49AHXTE33
103F26JPI4078
103103TX2EW18
103W702WDXJ43
103H5XB4Y8F90
10333UUDJRB55
103OAR3B31X27
103A8OCKOLT14
10356LMS8VG32
103RDHV0TFC81
103CBE4Z4P841
103Y9BD7P9U10
1030NR4A3PL58
103MKOE9EZH28
1037ILNHYI455
103TQIWPJT078
103OOF5O4CM31
1039LCEWOMI73
103VT9O596E62
103GR56DKG169
103BO2GC4QX54
103XWZPKPAJ32
103JUWYSAKF32
1034ST7RU4B2
103QZQHZFEY12
103LXNQ8QYU20
10364KZGB8G15
103S2H8FVSC32
103D0EING2859
10387BRV0LU42
103U58A4LWQ60
103F35J36FD20
1031ASSBHP972
103N8P1J1950
103I6MAIMJR28
1033DJKQ73N30
103PBGTZRDA73
103A9D27CN60
1035G9B6N7S60
103RE6LE8HO78
103CC33MS1K63
103YK0DLDB713
103JHXMTXV346
103FFUV2I5P4
103TR7V374C24
103OP45BSEZ68
103AW1EKCYV61
103VUYNIN8R59
103HSUWR8SD18
1032ZR6ZT2916
103XXOFYDMW34
103JVLO6YWS98
10342IXEIGO34
103Q0FGNTQA34
103LYCPLE0610
103759ZUZKT23
103S3682JUP64
103E13H14EL76
103Z90Q9PO721
103U6XZH07339
103GEU9QKIQ26
1031CQIO51M99
103N9NRXPBI35
103IHK05AV43
1034FHJDV5014
103PDESCFPN85
103BK22KQZJ22
103WIYBTBJF2
103RGVKSWT182
103DNST0GDX8
103YLP381NJ57
103KJMCGLXF35
103FQJLFXH295
1030OGUOHRY75
103MMDDW2AU32
1038TAMVMLG79
1031P5U152B88
103MX239QM735
1038UZCIAWT75
103TSWLQV6P97

 

 

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
Microsoft v-yulgu-msft
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))

Add [Measure1]to visual level filters, add [Measure2] in a card visual.

1.PNG

 

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.

View solution in original post

3 REPLIES 3
tnrahim
Frequent Visitor

Re: Need Help Creating a measure

bump

Microsoft v-yulgu-msft
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))

Add [Measure1]to visual level filters, add [Measure2] in a card visual.

1.PNG

 

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.

View solution in original post

Highlighted
tnrahim
Frequent Visitor

Re: Need Help Creating a measure

Thank you @v-yulgu-msft

 

Your answer was very helpful! I added one more measure to get the number of tickets greater than the current age.

 

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

 

 

 

 

 

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

Community Summit North America

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

Top Solution Authors