Forum Discussion
Filtered Count from Another Table
Hi,
New user to PowerBi here and I am having a very hard time getting what I think should be a simple calculation.
I have 2 tables. 1 is "Orders" and another is "Incidents" that are linked by the "Order #". I am trying to combine the two tables into a chart where it shows the number of "Pending" incidents per month (by "Incident Month") in a bar graph and the line graph will show the incident rate % (the number of "Pending" incidents dvided by the number of "Pending and "Service Request" Orders). Example of the desired graph shown below.
The bar graph is simple but I am having a lot of trouble getting the incident rate. I am having issues getting a count of the number of orders. All the things I have tired do not filter by month. I seem to just get a single number that doesnt break down into the months.
Can anyone explain what I am doing wrong?
ORDERS TABLE
| Order # | Order Date | Status | Type |
| O1 | 2-Oct-18 | Pending | Service Request |
| O2 | 15-Oct-18 | Pending | Return |
| O3 | 18-Oct-18 | Cancelled | Service Request |
| O4 | 21-Oct-18 | Pending | Service Request |
| O5 | 27-Oct-18 | Pending | Service Request |
| O6 | 31-Oct-18 | Pending | Service Request |
| O7 | 1-Nov-18 | Pending | Service Request |
| O8 | 4-Nov-18 | Pending | Service Request |
| O9 | 5-Nov-18 | Pending | Service Request |
INCIDENTS TABLE
| Incident # | Order # | Incident Date | Status |
| I1 | O1 | 5-Oct-18 | Pending |
| I2 | O1 | 5-Oct-18 | Pending |
| I3 | O2 | 5-Oct-18 | Pending |
| I4 | O3 | 20-Oct-18 | Cancelled |
| I5 | O4 | 1-Nov-18 | Pending |
| I6 | O6 | 22-Nov-18 | Pending |
The relationship will work. Just many to many can lead to ambiguous models. Anyhow, here's how I approached this one:
1. You are going to need a data table that will work as filter for Incidents and Orders:
make sure to use fields from that table as your slicers, filters, ect.
Then write a measure for Pending Incidents:
Pending Incidents = CALCULATE(COUNTROWS(Incidents),Incidents[Status]="Pending")
Then one for Pending and Service request from the Orders table:
Pending and Service Request = CALCULATE(COUNTROWS(Orders),Filter(Orders,Orders[Status]="Pending" && Orders[Type] = "Service Request"))
Then one last one to divide them out:
Pending Incidents divided by Pending and Service = DIVIDE([Pending Incidents], [Pending and Service Request])
Here's the final table. I cannot for life of me find the combo chart though:
7 Replies
I don't have access to the actual relationship dirgram, but I know that the only relationship between the two tables is the Order#
Hi,
Sorry if this is a repost but my original thread seems to have been removed.
New user to PowerBi here and I am having a very hard time getting what I think should be a simple calculation.
I have 2 tables. 1 is "Orders" and another is "Incidents" that are linked by the "Order #". I am trying to combine the two tables into a chart where it shows the number of "Pending" incidents per month (by "Incident Month") in a bar graph and the line graph will show the incident rate % (the number of "Pending" incidents dvided by the number of "Pending and "Service Request" Orders). Example of the desired graph shown below.
The bar graph is simple but I am having a lot of trouble getting the incident rate. I am having issues getting a count of the number of orders. All the things I have tired do not filter by month. I seem to just get a single number that doesnt break down into the months.
Can anyone explain what I am doing wrong? What formula do I need in order to get the count of the order per month?
ORDERS TABLE
Order # Order Date Status Type O1 2-Oct-18 Pending Service Request O2 15-Oct-18 Pending Return O3 18-Oct-18 Cancelled Service Request O4 21-Oct-18 Pending Service Request O5 27-Oct-18 Pending Service Request O6 31-Oct-18 Pending Service Request O7 1-Nov-18 Pending Service Request O8 4-Nov-18 Pending Service Request O9 5-Nov-18 Pending Service Request INCIDENTS TABLE
Incident # Order # Incident Date Status I1 O1 5-Oct-18 Pending I2 O1 5-Oct-18 Pending I3 O2 5-Oct-18 Pending I4 O3 20-Oct-18 Cancelled I5 O4 1-Nov-18 Pending I6 O6 22-Nov-18 Pending
Desired Chart
