Forum Discussion

tnrahim's avatar
tnrahim
Frequent Visitor
7 years ago
Solved

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 a...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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.

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
7 years ago

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.

 

Best regards,

Yuliana Gu

tnrahim's avatar
tnrahim
Frequent Visitor
7 years ago

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))