cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WillEyedowin Frequent Visitor
Frequent Visitor

Divide two columns from two different tables using a filter

Hi All,

 

I am trying to divide two columns from two different tables and use a common filter in a third table.

 

The common column or filter is the Store Name.

 

The two data columns which I would like to use for the calculation are Sold ending 26th August[Quantity Sold] and Dispatched[Quantity Dispatched].

 

 

I would like to divide the sum of  Sold ending 26th August[Quantity Sold](B) for a Stores[Store Name](C) by the sum of  Dispatched[Quantity Dispatched](A)

 

I have tried using Related() and RelatedTable() and a few other options but I am stuck.

 

Dispatched table where example A data is found:

 dispatched table.PNG

Sold ending 26th August Table where B sample data is found

sold table.PNG

 

Stores table - where C sample data is found - I want to use this to filter the other 2 tables

stores table.PNG

 

This is essentially how I want this report to work- Divide the first column by the second and another column stores the result.

sample data.PNG 

How the relationships are currently set up

relationships.PNG

Tables and the columns used

Tables.PNG

 

Also, let me know if any more information is needed as I am new to these forums and rather new to PowerBI.

 

Any help would be greatly appreciated.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Divide two columns from two different tables using a filter

Sample data in a form that can be copied and pasted is always helpful. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

That being said, I'm thinking something along the lines of:

 

Measure = DIVIDE(SUMX(RELATEDTABLE('Sold ending 26th August'),[Quantity]),SUMX(RELATEDTABLE('Dispatched'),[Quantity Dispatched]))


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

1 REPLY 1
Highlighted
Super User
Super User

Re: Divide two columns from two different tables using a filter

Sample data in a form that can be copied and pasted is always helpful. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

That being said, I'm thinking something along the lines of:

 

Measure = DIVIDE(SUMX(RELATEDTABLE('Sold ending 26th August'),[Quantity]),SUMX(RELATEDTABLE('Dispatched'),[Quantity Dispatched]))


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 179 members 2,081 guests
Please welcome our newest community members: