Create a graph from 3 table visuals which use the same data (but are filtered differently)

[christinek]

Hi all, hope I can explain this well enough. I have created 3 tables as per below.

The tables use the same data, but each one uses a different filter.

Table 1 shows jobs for FY23 (filtered on "Std Matter" slicer).

Table 2 shows jobs for FY22 (filtered on "Std Matter" slicer).

Table 3 shows jobs for FY21 (filtered on "Std Matter" slicer).

I wish to show a comparison "# Jobs In" per month for FY23, FY22 and FY21.

I thought maybe a stacked line graph showing FY23 data as the stack and then FY22 and FY21 as lines.

But I am not sure how to achieve this.

I think I need 3 measures for the data in each table as follows:

1) # Jobs In FY23

2) # Jobs In FY22

3) # Jobs In FY21

but am not sure how to write the code for each one to include the relevant filter.

Or if there is an easier or better way to show the compariosn, open to suggestions.

Any assistance appreciated.

[halfglassdarkly]

Hi Christine,

You'll need to sort the month column used in your graph by another column for financial period.

You can add a financial period column like this:

FP =
If(MONTH('your date table'[Date])>6,
MONTH('your date table'[Date])-6,
MONTH('your date table'[Date])+6)

Then select the month column you're using in your graph and set it to Sort By the new FP column. See https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column?tabs=powerbi-deskto... for an explanation of Sort By.

This article explains managing interactions between visuals: https://learn.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions?tabs=p... 

I'd suggest checking out a few YouTube videos on PowerBI Interactions to get the gist of how they work as it's going to be easier to see it demonstrated.

[halfglassdarkly]

Hi Christine, you're on the right track.

create a measure for each FY like this:

Running Total FY23 =
CALCULATE(count('your fact table'[Job ID],
'your date table'[FY]=2023,
'your date table'[Date] <= max('your date table'[Date])

This assumes you have a date dimension table, but if not just replace both 'your fact table' and 'your date table' with the name of the table where your date/month/year fields are stored.

I've also assumed you already have a column for FY. If not you'll probably want to add one. E.g if your end of financial year is 30 June, add a calculated column in the same table as your date field:

FY =
If(MONTH('your date table'[Date])>6,
YEAR('your date table'[Date])+1,
YEAR('your date table'[Date]))

[christinek]

Thank you so much. I am close now. I added the FY column and 3 measures:

I added graph and have 2 issues with it:

1) I want it to start by FY with July to June instead of Jan to Dec; and

2) I am not sure on how I do the "Edit" interactions for figures to work. I currently have graph interacting with orange std matter selection and that part of the graph (orange columns) is correct. But other 2 lines are not. If I interact graph with all 3 std matter selections, nothing displays in graph.

Any further assistace would be much appreaciated.