Issue Multi-studios data filter in Power Bi Desktop

[NhiLe297]

Hi Everyone,

I am new to Power Bi and has been trying to created a report showing cross studio sales for my company. 

The data exported from Xero. Within an invoice, there is Job Number with studio collumn and each invoice line item belong to a studio as well. Ex:

Job No. on invoice is Bxxxx, belong to studio B, invoice line item can be as below:

INV-20000

Date	Desc	Amount (exc Tax)	Studio
(blank line w Job No.)	Job No: Bxxxx
xx/xx/xxxx	lineitem 1	$100	B
xx/xx/xxxx	lineitem 2	$150	C

In the report I want to show the below for each month:

Invoice No.	Invoice Date	Job No.	Studio	Gross Sales	Studio B sales	Cross Studio sales
INV-20000 line item 1	xx/xx/xxxx	Job No: Bxxxx	B	250	100	0
INV-20000 line iteam 2	xx/xx/xxxx	Job No: Bxxxx	B		0	150
			TOTAL	250	100	150

90% of the report has been done but I am having issues with studio filtering.

I have created different tabs for each studio.

The filters are: 

• By Month/Year
• By Studio

Columns come from the below data tables:

• Invoice_plus table -->Invoice No., Invoice Date, 
• invoice_job --> Job Number
• invoicelineitem table --> studio, gross sale (sum of lineamount column), Studio B sales (I created a column for only studio B amount)

At the moment the Studio filter was from another table call Studio table. This table includes Job Studio (ex: studio A, B, C) for each studio Job Code (ex: A, B, C ....these are the first letter of job number) with the studio name as per job code.

Issue 1: When I filter using the above Studio filter only the invoice of which Job No. starts with correspondence studio code shows (Ex: selecting studio B, only job number start with Bxxx show). This is a good thing as the cross studio sales collumn works, the issue is there is line amount belong to another invoice, but this invoice has Job No start with C, within this invoice, 1 line amount belong to studio B, but this invoice does not appear under studio B filter, it only appear if I select studio C in filter--> which make the total amount in correct for all collumns.

Issue 2: if I filter using the Studio collumn from invoicelineintem table, only invoice line item with the filtered studio shows, ex: select studio B in filter, I can see all line items that are studio B (in Xero this is the studio column from the invoice, which show studio B)

This avoided the problem in issue 1 but created bigger problem where there were no cross studios sales --> again the total amount is incorrect and also defeat the whole purpose of creating the report in the first place.

I really need help with the above issues and the result I am aiming at is for the report to show as below:

Second invoice for result example

INV-20001

Date	Desc	Amount (exc Tax)	Studio
(blank line w Job No.)	Job No: Cxxxx
xx/xx/xxxx	item 1	$200	B
xx/xx/xxxx	item 2	$250	C
xx/xx/xxxx	item 3	$300	C

Under studio B Tab the below should show (and the other way around for studio C Tab

Invoice No.	Invoice Date	Job No.	Studio	Gross Sales	Studio B sales	Cross Studio sales
INV-20000 line item 1	xx/xx/xxxx	Job No: Bxxxx	B	250	100	0
INV-20000 line iteam 2			C		0	150
INV-20001 line item 1		Job No: Cxxxx	B	$750	$200
INV-20001 line item 2			C			$250
INV-20001 line item 3			C			$300
			TOTAL	250	100	150

Thank you!

[doremon2111]

The 1st main point of your problem is that you need to restructure your source data table. 

Here is one of my example of table InvoiceLine.

What should be noticed:

1. Each line is corresponding to 1 lineitem.
2. I added 4 columns: Job No, Invoice No, Original Studio, Cross Studio. You have to create two separate columns: one for Original Studio, and one for Cross Studio. 
3. Create a table Studio --> Create Relationship between Original Studio Key vs. Job Code (in table Studio)

With this, you can solve your 1st issue. 

The 2nd main point is that your out put table is being incorrectly showed. Power BI isn't like excel, you cannot visualize the table like this. 

You cannot show the total amount of invoice in the same line with ItemLine. It doesnt make sense. 

Here is what i created with Source Data table above 

[lbendlin]

First thing you want to do is improve the sructure of your source data. You should not have "blank lines with data". If the job number is the same throughout an invoice then you need to add the job number as a field to the invoice details.

[NhiLe297]

Thank you for your feedback.

At the moment the blank lines do not create any issues and I have managed to make the visualisation of the report not showing the blank lines. 

Do you have any recommendation on how to fix the studio filtering issues. Main goal is to show correct total cross studio sales for each studio.

Thank you!