Filtered Count from Another Table

[achen]

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

[achen]

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

[Anonymous]

getting a number that seems to be a total and is repeating generally means there is a problem wiht the  relationships between the two tables.  Have a screenshot of the diagram view? 

[achen]

I don't have access to the actual relationship dirgram, but I know that the only relationship between the two tables is the Order#

[Anonymous · Accepted solution]

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:

[Anonymous]

That's a many to many relationship, which can have some interesting results.  Do you  have the option to create another table using Power Query?

[achen]

Yes I could create another table if needed. But I am not sure what kind kind of table I would need to create in order to get what I am looking for.

[achen]

Sorry and I just noticed an error in my table above. The order # will always be unique. I had a typo there where O8 showed twice. The incident number will always be unique as welll. However htere can be mutliple incidents linked to one order.