Calculating count of sub-tickets

[Anonymous]

Hello All, 

I'm trying to calculate how many sub-requests I have per request. Our structure (issue_type) is request, vendor, and sub-request (this is language). Each vendor can have multiple sub-requests (languages). I'm trying to get the the total requested languages per request. 

Please note sub-requests have parent ID in them, however vendor does not have child ticket field in there. The connection between vendor & Request is found in tab two outward/inward connections. 

I am attaching xls for reference.

Thank you very much for your help 

Issue_Id	Key	Issue_Type_Id	Issue_Type_Name	Gross_Wordcount	Target_Language_Selected	Unit_rate	Fees	Language_Quote	Weighted_wordcount	Total_Quote	Vendor_Quote	Vendor	Parent_Issue_Key	Parent_Issue_Type_Name
342144	LOCAT-29209	10606	Request	200		0	0	0	0	€ 111.43	0
342158	LOCAT-29219	13809	Vendor	0		0	0	0	0	€ 0.00	18.96	osmicka Hong Kong
342157	LOCAT-29218	13809	Vendor	0		0	0	0	0	€ 0.00	6.083	Dimlight
342156	LOCAT-29217	13809	Vendor	0		0	0	0	0	€ 0.00	11.06	Latis Global
342155	LOCAT-29216	13809	Vendor	0		0	0	0	0	€ 0.00	13.035	osmicka Japan
342154	LOCAT-29215	13809	Vendor	0		0	0	0	0	€ 0.00	5.135	Quoted
342153	LOCAT-29214	13809	Vendor	0		0	0	0	0	€ 0.00	5.309	Qloc
342152	LOCAT-29213	13809	Vendor	0		0	0	0	0	€ 0.00	12.166	Local Heroes
342151	LOCAT-29212	13809	Vendor	0		0	0	0	0	€ 0.00	18.128	Seamonkeys
342150	LOCAT-29211	13809	Vendor	0		0	0	0	0	€ 0.00	10.428	Mouse Power
342164	LOCAT-29225	13808	Sub-Request	0	POLISH (PL)	0.064	0.05	5.309	79	€ 0.00	0	Qloc	LOCAT-29214	Vendor
342170	LOCAT-29231	13808	Sub-Request	0	THAI (TH)	0.12	0	9.48	79	€ 0.00	0	osmicka Hong Kong	LOCAT-29219	Vendor
342169	LOCAT-29230	13808	Sub-Request	0	TRADITIONAL CHINESE (TCH)	0.12	0	9.48	79	€ 0.00	0	osmicka Hong Kong	LOCAT-29219	Vendor
342167	LOCAT-29228	13808	Sub-Request	0	KOREAN (KR)	0.14	0	11.06	79	€ 0.00	0	Latis Global	LOCAT-29217	Vendor
342165	LOCAT-29226	13808	Sub-Request	0	PORTUGUESE (BR)	0.065	0	5.135	79	€ 0.00	0	Quoted	LOCAT-29215	Vendor
342168	LOCAT-29229	13808	Sub-Request	0	SIMPLIFIED CHINESE (SCH)	0.07	0.1	6.083	79	€ 0.00	0	Dimlight	LOCAT-29218	Vendor
342166	LOCAT-29227	13808	Sub-Request	0	JAPANESE (JP)	0.15	0.1	13.035	79	€ 0.00	0	osmicka Japan	LOCAT-29216	Vendor
342163	LOCAT-29224	13808	Sub-Request	0	DUTCH (NL)	0.14	0.1	12.166	79	€ 0.00	0	Local Heroes	LOCAT-29213	Vendor
342162	LOCAT-29223	13808	Sub-Request	0	ITALIAN (IT)	0.11	0.1	9.438	78	€ 0.00	0	Seamonkeys	LOCAT-29212	Vendor
342161	LOCAT-29222	13808	Sub-Request	0	SPANISH (ES)	0.1	0.1	8.69	79	€ 0.00	0	Seamonkeys	LOCAT-29212	Vendor
342160	LOCAT-29221	13808	Sub-Request	0	GERMAN (DE)	0.12	0.1	10.428	79	€ 0.00	0	Mouse Power	LOCAT-29211	Vendor
342159	LOCAT-29220	13808	Sub-Request	0	FRENCH (FR)	0.128	0.1	11.123	79	€ 0.00	0	Keywords France	LOCAT-29210	Vendor

[ERD]

Hi @Anonymous ,

One of the ways to achieve this (calculate sub-requests per request) is to:

1. Create 2 calculated columns:

_Parent_Id =
SWITCH (
    Data[Issue_Type_Name],
    "Request", Data[Issue_Id],
    "Sub-Request", Data[Parent_Issue_Id],
    "Vendor",
        LOOKUPVALUE (
            Connections[Issue_Link_Id],
            Connections[Issue_Id], Data[Issue_Id]
        )
)

_Request =
VAR _path = PATH ( Data[Issue_Id], Data[_Parent_Id] )
VAR _item = PATHITEM ( _path, 1 )
RETURN
    _item

2. Create a measure:

subrequests =
VAR current_req = SELECTEDVALUE ( Data[Issue_Id] )
VAR subs =
    CALCULATE (
        COUNT ( Data[Issue_Id] ),
        Data[_Request] = current_req,
        Data[Issue_Type_Name] = "Sub-Request",
        ALLEXCEPT ( Data, Data[_Request] )
    )
RETURN
    subs

[Anonymous]

Hey @ERD , 

I think I almost have it 🙂 except i wanted to ask if I can create a new column in stead of measure? Or Should I copy it over so I can compare it visually? (I don't see the measure as a new column) And i was going to figure the cost savings now by doing (#of subrequests * gross word count * .15 subtracted by total cost) to get cost savings, and I'm stuck on how to add or visualize on this measure. Thanks a lot 🙂 

[ERD · Accepted solution]

@Anonymous , so you need a calculated column, not a measure?

1. Set a Whole number type for _Request column:

2. Use this code:

subrequests = 
VAR current_req = Data[Issue_Id]
VAR subs =
    CALCULATE (
        COUNT ( Data[Issue_Id] ),
        Data[_Request] = current_req,
        Data[Issue_Type_Name] = "Sub-Request",
        ALLEXCEPT ( Data, Data[_Request] )
    )
RETURN
    subs

3. If you need any other calculations, just use the appropriate columns:

subrequests = 
VAR current_req = Data[Issue_Id]
VAR subs =
    CALCULATE (
        COUNT ( Data[Issue_Id] ),
        Data[_Request] = current_req,
        Data[Issue_Type_Name] = "Sub-Request",
        ALLEXCEPT ( Data, Data[_Request] )
    )
RETURN
    subs * Data[Gross_Wordcount] * 0.15 - Data[Total_Quote]

[Anonymous]

Hey @ERD , 

Can I ask how you set up relations in connections & Data? I'm trying to think where I could have done something wrong, I used the data/formula you gave me, however it does not populate the Vendor tickets, furthermore _parent_ID & _Request are populated with the ID of the ticket itself not parent/child. 

[123abc]

Create a New Measure: You'll want to create a new measure that calculates the count of sub-requests per request.

TotalRequestedLanguages =
COUNTROWS(
FILTER(
'YourTableName',
'YourTableName'[Issue_Type_Name] = "Sub-Request"
)
)

1. Replace 'YourTableName' with the name of your table where you imported the data.

2. Visualize the Data:

   • Drag and drop the Issue_Id and TotalRequestedLanguages into a table or a matrix visualization.
   • Now, you should see a list of Issue_Id along with the total count of sub-requests (languages) for each request.

3. Further Analysis (Optional):

   • You can further break down this analysis by vendor or other dimensions if needed. You can create additional measures or use slicers to filter your data accordingly.

4. Refining the Data Model:

   • Make sure that your relationships in Power BI are correctly set up to establish the connection between requests and sub-requests. You might need to set up relationships between Issue_Id and Parent_Issue_Key if it's not done automatically.
   • If there are any inconsistencies or errors in your data, you might need to clean or transform your data in Power Query Editor before proceeding with calculations.

Remember, the steps provided give you a basic approach to determine the count of sub-requests per request. Depending on your specific requirements and the complexity of your dataset, you might need to adjust or refine these steps accordingly.

[Anonymous]

Hey @123abc , 

Thanks for the reply, however this only gives the sum/even when i do count it does not calculate the sub-request per request. 

[Fowmy]

@Anonymous 

It will be helpful ifyou could share the expected output based on the attached sample. Add an additional sheet with the results that you need.