Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Syndicate_Admin
Administrator
Administrator

Analysis Percentage of Expenses over total sales.

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.

ControlGestioCS_0-1737573857801.png

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.

ControlGestioCS_1-1737573966321.png

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

3 REPLIES 3
ToddChitt
Super User
Super User

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.




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Syndicate_Admin
Administrator
Administrator

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

ControlGestioCS_0-1737633636613.png

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

ControlGestioCS_1-1737633699786.png

The Order table is the table that connects both dimensions.

ToddChitt
Super User
Super User

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.




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.