Calculating cost based on average ticket count

[bluez]

Hello,

I need some help with calculating the cost of tickets per site. 

A base rate of 100,000 for each agent (e.g. Alpha costs the company 100,000 to employ for the year, etc.) then take that rate and divide by the total tickets they created for each year to find out how much it costs for each ticket they created for that year. Then, take that cost per ticket and do a count of how much tickets they had for a particular site and do a sum of cost per site.

What I would like is something below: 

(100,000 / Total ticket Count for each year per agent) * (Ticket count for account that year) = Cost of supporting account per year(or month)

Sample Data:

Ticket Number	Agent	Date Opened	Account
001	Alpha	1/1/2022	Smith Co.
002	Alpha	5/9/2022	Johnson LLC
003	Beta	7/19/2023	Smith Co.
004	Delta	12/13/2023	Smith Co.
005	Delta	8/11/2022	Johnson LLC.
006	Beta	9/22/2023	Johnson LLC.
007	Alpha	11/29/2023	Happy Corp
008	Alpha	12/16/2022	Happy Corp
009	Beta	3/20/2022	Happy Corp

I also have created data sheets to try and break it down but not quite sure how to relate the data properly back to the accounts.

Sample Data Ticket Count Per Year:

Agent	2021	2022	2023	2024
Alpha	326	128	96	34
Beta	485	267	234	110
Delta	36	151	364	402

Sample Data Cost Per Ticket Per Year:

Agent	2021	2022	2023	2024
Alpha	$306.75	$781.25	$1041.66	$2941.17
Beta	$206.18	$374.53	$427.35	$909.09
Delta	$2777.77	$662.25	$274.73	$248.75

[bluez]

Hello @v-rzhou-msft,

I did an unpivot and got the new measure which is helpful. That solves part of my problem. How do I get that new measure to multiple by the number of tickets per account over the year and get a sum of the total cost? Since I would like the visual to be a column chart or something similar.

What I want:
new measure * number of tickets for account (but for each agent).
e.g.

Alpha cost $306.75 for each ticket created in 2021 but created 10 tickets for Smith Co.
Alpha total for Smith Co:

306.75 * 10 = 3067.50

Beta cost $206.19 for each ticket created in 2021 but created 5 tickets for Smith Co.

Beta total for Smith Co:

206.19 * 5 = 1030.95 

Total cost for Smith Co. = Alpha total + Beta Total + etc...

Then sum them all together over the year and to be able to slice the data with an account slicer to show the different accounts total throughout the years.

[v-rzhou-msft]

Hi @bluez ,

I suggest you to do some transformation on your Sample Data Ticket Count Per Year in Power Query Editor.

Use Unpivot function:

Data model:

Measure:

Measure = 
10000 / SUM('Ticket Count Per Year'[Value]) * CALCULATE(COUNT('Table'[Account]),ALL('Table'))

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.