Calculating percentage with affect of active relation and inactive relation

[Anonymous]

Hello,

I'm trying to calculate percentage from a facttable containing ticket audits to answer the questions "From which departments do we get our tickets in percentage of total departments involved in tickets" and "Which departments have been assigned to our ticket when we solved it in percentage of total departments involved in tickets (excluding our own department)".

The table FactTicketAudit looks like this:

TicketKey	DepartmentKey	PreviousDepartmentKey	DateKey	RowNbrAsc	RowNbrDesc	ResolvedDepartmentKey
T1	ServiceDesk	null	2024-01-01	1	8	IT
T1	IT	ServiceDesk	2024-01-01	2	7	IT
T1	Payroll	IT	2024-01-01	3	6	IT
T1	ServiceDesk	Payroll	2024-01-01	4	5	IT
T1	IT	ServiceDesk	2024-01-01	5	4	IT
T1	Marketing	IT	2024-01-01	6	3	IT
T1	ServiceDesk	Marketing	2024-01-01	7	2	IT
T1	IT	ServiceDesk	2024-01-01	8	1	IT
T2	IT	null	2024-01-01	1	3	ServiceDesk
T2	Marketing	IT	2024-01-01	2	2	ServiceDesk
T2	ServiceDesk	Marketing	2024-01-01	3	1	ServiceDesk

DimDepartment

DepartmentKey	DepartmentName
Marketing	Marketing full name
IT	IT full name
ServiceDesk	Service Desk full name
Payroll	Payroll full name
Dummy1	Dummy 1 full name
Dummy2	Dummy 2 full name

"From which departments do we get our tickets in percentage of total departments involved in tickets"

If I set the DimDepartment slicer to IT, the barchart should show ServiceDesk on x-axis and 100% on y-axis. Because everytime the IT department has been assigned a ticket it has come from ServiceDesk.

If I set the slicer to ServiceDesk the x-axis should show Marketing and Payroll and y-axis 67% and 33%.

And so on for other departments choosen in the slicer.

The other question to be answered (in another barchart of course)

"Which departments have been assigned to our ticket when we solved it in percentage of total departments involved in tickets (excluding our own department)"

From the example there are only two departments that have solved tickets: IT and ServiceDesk.

If I set the slicer to IT the barchart should show departments ServiceDesk (60%), Marketing (20%) and Payroll (20%).

If I set the slicer to ServiceDesk the barchart should show Marketing (50%) and IT (50%).

Since this is an example I've added both a rownumber descending column and ResolvedDepartmentKey to use in an active or inactive relation. I can implement either of the columns to the source.

I've spent way to many hours using ChatGPT to try to solve this but without any luck. The problem is the relation between the fact table and DimDepartment. As soon as I choose some department in the slicer the visuals are filtered to that specific department and not showing "departments involved in a ticket except the selected one".

If possible I'd like the relation between FactTicketAudit.DepartmentKey and DimDepartment.DepartmentKey to be active. That way I can build other visuals, but if needed the relation can be inactive.

Who do I do to achieve this?

FactTicketAudit.pbix

[ThxAlot]

The moment you could wrap your head around the logic, DAX would be under your belt.

[Anonymous]

Thank you for your response!

I realise that I wasn't clear in my description, cuz I can't use the values of DepartmentKey, PreviousDepartmentKey or ResolvedDepartmentKey from the fact table as x-axis values in the barchart. In my exampel file those keys as in readable format, but in my production data they are not. So the x-axis has to come from the DimDepartment.DepartmentName field. I guess that's why the relations are important and also why it makes it a bit tricky.

[FarhanJeelani]

Hi @Anonymous ,

To achieve this in Power BI, you can use DAX measures with calculated filters to bypass the slicer’s default filtering behavior. Here's a simplified approach:

1. Measure for "From which departments do we get our tickets"

This calculates the percentage of tickets received from other departments when a department is selected.

TicketsFromDepartments = 
VAR SelectedDept = SELECTEDVALUE(DimDepartment[DepartmentKey]) 
VAR TotalTickets = COUNTROWS(FactTicketAudit)
VAR TicketsByDept = 
    CALCULATE(
        COUNTROWS(FactTicketAudit),
        FactTicketAudit[DepartmentKey] <> SelectedDept
    )
RETURN
DIVIDE(TicketsByDept, TotalTickets, 0)

• Explanation:
  • SelectedDept stores the department selected in the slicer.
  • The measure calculates tickets where the DepartmentKey does not match the selected department.
  • Divides the result by the total tickets for percentages.

2. Measure for "Which departments have been assigned to our tickets when solved"

This calculates the percentage of departments assigned tickets (excluding the selected department).

TicketsAssignedToOtherDepts = 
VAR SelectedDept = SELECTEDVALUE(DimDepartment[DepartmentKey]) 
VAR TotalAssigned = COUNTROWS(FactTicketAudit)
VAR AssignedByOtherDepts = 
    CALCULATE(
        COUNTROWS(FactTicketAudit),
        FactTicketAudit[ResolvedDepartmentKey] <> SelectedDept
    )
RETURN
DIVIDE(AssignedByOtherDepts, TotalAssigned, 0)

• Explanation:
  • Filters out rows where ResolvedDepartmentKey matches the selected department.

3. Enable the Active Relationship

• Keep the active relationship between FactTicketAudit[DepartmentKey] and DimDepartment[DepartmentKey] for general filtering.
• Use measures to override default slicer filtering.

4. Build the Visuals

• First Bar Chart:
  Use TicketsFromDepartments as the value and DimDepartment[DepartmentName] on the X-axis.
  Set a slicer on DimDepartment[DepartmentKey].

• Second Bar Chart:
  Use TicketsAssignedToOtherDepts as the value and DimDepartment[DepartmentName] on the X-axis.
  Apply the same slicer.

Key Points

• Measures ignore slicer filtering by explicitly excluding the selected department.
• Keep relationships active for general filtering but control exceptions through measures.
• Add data categories to ensure the visuals update dynamically.

Let me know if you need further help refining the DAX or visuals!

Please mark this as solution if it helps. Appreciate Kudos.

[Anonymous]

Thank you so much for your respone. Unfortunately your measures doesn't work.

1. Measure for "From which departments do we get our tickets"

This measure doesn't return anything, regardless if something is selected in the slicer or not

2. Measure for "Which departments have been assigned to our tickets when solved"

This measure returns something but if I set the slicer to IT it returns 25% for IT department, when in fact it should return ServiceDesk 60%, Marketing 20% and Payroll 20%

Did you try my provided pbix-file?