Comparing the number of parent/child occurrences in Line Chart

[gogrizz]

I have a dozen+ networks (franchises) and 2-300 companies, all of which are distributed amongs the networks.

Each company is assigned to a network, making it a parent/child type relationship.

I also have 200k events in a fact table.  The fact table has columns that indicate the network and company linked to the event.

My report has a slicer to choose the network, a slicer to choose the company, and a line chart.

I want to display the number of events on a line chart with the event date on the x-axis.  I'm trying to get one line to represent the number of events related to the company, and a second line that shows the moving average of all events that are linked to the network.  

The end goal is to show a company's event history (line #1), compared to the average of all companies within that network (line #2).  I've tried a bunch of ways and have searched the forum, but can't find and answer that'll work.

Thanks!

[parry2k · Accepted solution]

@gogrizz I think this is what you need:

Event Count = COUNTROWS ( Table ) --this is what you will use for Line 1

Avg Event Count for selected network = 
CALCULATE 
    AVERAGEX ( VALUES ( Table[EventDate] ), [Event Count] ),
    REMOVEFILTERS ( NetworkCompanyTable[Company] )
)

if this doesn't work, I would recommend pasting sample data in a table format to further look into it.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.