Filter items and finding the average of Days

[Sriku]

Hi ,

I want to filter tickets group having max Ticket indexing and show in graph avergae if "Update ticket group" columns  -- "General" is selected the show value as 0.01, if second row "Pay" is selected it should show 0.01, third row "Time" is selected then 0.07 and so on

Ticket Indexing	Index2	Ticket ID	Update ticket group	Update - Timestamp	Update ticket group - Start	Update ticket group - Prev	Is Hop	Days in Ticket group	Update ticket group - Next	Hops Count	Update ticket group - End
1	1	258298	General	2025-03-10 08:47:43	General		1	0.01	Pay	3	Pay
2	2	258298	Pay	2025-03-10 08:59:01	General	General	1	0.07	Time	3	Pay
3	3	258298	Time	2025-03-10 10:35:13	General	Pay	1	1.15	Pay	3	Pay
4	0	258298	Pay	2025-03-11 14:12:40	General	Time	0	2.74		3	Pay

[v-echaithra]

Hi @Sriku ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @Sriku 

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @Sriku ,

Di you try the above steps provided by the super user?

[v-echaithra]

Hi @Sriku ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[Sriku]

@v-echaithra , Still looking for further assistance

[bhanu_gautam · Accepted solution]

@Sriku 

Create a new calculated column to assign the values based on the "Update ticket group" column. Go to the "Modeling" tab and select "New Column". Use the following DAX formula:

DAX
Ticket Group Value =
SWITCH(
TRUE(),
'Table'[Update ticket group] = "General", 0.01,
'Table'[Update ticket group] = "Pay", 0.01,
'Table'[Update ticket group] = "Time", 0.07,
'Table'[Update ticket group] = "Other", 0.07, // Add other conditions as needed
BLANK()
)

Create a measure to filter the data to only include the rows with the maximum "Ticket Indexing" for each "Ticket ID". Go to the "Modeling" tab and select "New Measure"

DAX
Max Ticket Indexing =
CALCULATE(
MAX('Table'[Ticket Indexing]),
ALLEXCEPT('Table', 'Table'[Ticket ID])
)

Create a measure to calculate the average of the values from the calculated column.

DAX
Average Value =
AVERAGEX(
FILTER(
'Table',
'Table'[Ticket Indexing] = [Max Ticket Indexing]
),
'Table'[Ticket Group Value]
)

Add a new visual (e.g., a bar chart) to your report.
Drag the "Ticket ID" to the Axis.
Drag the "Average Value" measure to the Values.