cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
croberts21
Post Patron
Post Patron

2 fields are from 2 tables, how to use DIVIDE to calculate percent?

I have a simple table viz. I have 2 tables with data. I had to connect the 2 tables with a Calendar table full of dates which was made from GENERATESERIES. The date in the Calendar table was made into 1 month bins which is shown in column 1 of the table below. The values for "PE Cost" and "InvoiceAmt" are correct for each month. The table looks like this right now: 

croberts21_0-1656519911205.png

The measure "PE Cost" is from a table "Test2022". The measure "InvoiceAmt" is from a table called "Invoice". I want to take the column/measure "PE Cost" divided by "InvoiceAmt" to calculate a percent in "PE Percent". BUt when I try to make a measure on the "Invoice" table which is "PE Percent = DIVIDE(Test2022[PE Cost], Invoice[InvoiceAmt])" PBI will only show fields for the "Invoice" table. 

 

These tables are not connected in any way except by the intermediate Calendar table. The user wants to see the data by month. 

 

I've viewed 10+ hours of videos for PBI in general and looked at 10 pages for this issue but have not found a solution yet. How do I calculate "PE Cost" divided by "InvoiceAmt"?

 

Thank you!

1 ACCEPTED SOLUTION
croberts21
Post Patron
Post Patron

This formula worked. Yay!

croberts21_0-1656585963557.png

 

View solution in original post

3 REPLIES 3
croberts21
Post Patron
Post Patron

This formula worked. Yay!

croberts21_0-1656585963557.png

 

tamerj1
Community Champion
Community Champion

Hi @croberts21 

simply dividing the two measures should work. Are you sure yiu dividing the measures not the columns? What is dax code of each measure?

Both numeric columns are columns with a different name than the query column, not measures. So the "PE Cost" column is just "PE Cost = Test2022[Total Cost]" with different formatting for the date. I was unable to get measures to work with any SUM function in this case, and a measure cannot just point to a column without a calculation function. 

I need to use monthly bins to sum these numbers correctly. Since I use bins, I don't need to use measures. I could not get measures to work anyway for this case. 

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors