cancel
Showing results for
Did you mean:
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:

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
Post Patron

This formula worked. Yay!

3 REPLIES 3
Post Patron

This formula worked. Yay!

Community Champion

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

Post Patron

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.

Announcements

#### The Power BI Community Show

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

#### Ted's Dev Camp - July 28, 2022

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