Analysis Percentage of Expenses over total sales.

[Syndicate_Admin]

Hello, good afternoon everyone.

I ask you the following problem I am having when generating an indicator that in excel I can easily do.

I need to calculate the percentage of expenses over total sales. I have an Expenses table and a sales table. What I need is to know by spending group the percentage occupied over 100% of the sale.

The reality is that every measurement I try to perform gives me 0 or the result "Infinite". In excel it is as easy as a division, which I try to do in the same way in BI but generates an error.

I came to interpret that it may be an error in the base, because I have the expenses broken down by group of expenses and the sales are not, they are only opened x month and company name

[ToddChitt]

Start with two measures, each in their respective tables. One for Total Expenses and one for Total Sales.

Verify first that each of those are reporting correct numbers. 

Create a Table visual on a new page. Add to it the two measures created above. Add to the table an attribute (like Order Number) from the Orders table. So the Measures still make sense? Are you getting [Total Sales] by Order, and [Total Expenses] by Order> If so, then dividing one by the other should be trivial with a new measure.

[Syndicate_Admin]

The same thing that brings me empty data continues to happen to me.

I think the error may be in the database, I have both tables connected from a master with a one-to-many relationship.

The Order table is the table that connects both dimensions.

[ToddChitt]

In order for this to work properly, you need a common dimension that is on the ONE side of One-to-Many relationships to your Expenses and Sales. You need to pick ONE of those two tables to host the new Measure, and that Measure will only be sliced by that dimension.

Percentage of Expenses over Sales = DIVIDE ( [Total Expenses], [Total Sales] )

Where [Total Expenses] and [Total Sales] are each measures (summaries, I assume) defined in their respective tables.