cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jtownsend21 Member
Member

Calculation between two unrelated data sources

Hi all, I have two data sources that I cannot relate, but would like to be able to do something like the following. Is this possible using DAX (or any other functionality)?

 

Rev per Appeal.PNG

 

Revenue is calculated as a sum while appeals are calculated by a distinct count. Other than filtering to a given date range (month/qtr/year) there are no other filters. 

 

Thoughts? 

 

SEE SAMPLE DATA HERE

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculation between two unrelated data sources

Looking at this I'm not sure that I fully understand what you are trying to accomplish but what I would recommend would be to first create a New Table with the following formula:

 

Calendar = CALENDARAUTO()

This should create a calendar table that you can then relate to both of your other tables. Then you can create a slicer based upon this new Calendar table.

 

Then, you would have the following measures:

 

Revenue = SUM('Revenue'[NetRevenue])

Appeals = DISTINCTCOUNT('Appeals'[Appeal Id])

RevenuePerAppeal = DIVIDE([Revenue],[Appeals],0)

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

Proud to be a Datanaut!


4 REPLIES 4
Super User
Super User

Re: Calculation between two unrelated data sources

So, are those two calculations measures? If so, you should just be able to divide them. Sample data helps tremendously. 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


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

Proud to be a Datanaut!


jtownsend21 Member
Member

Re: Calculation between two unrelated data sources

They are calculations from two different tables which have no relationship and therefore I don't know of a way to simply divide them. 

 

Just updated the original post with a link to some sample data. Sorry I didnt include it on the first go around. Cheers

Super User
Super User

Re: Calculation between two unrelated data sources

Looking at this I'm not sure that I fully understand what you are trying to accomplish but what I would recommend would be to first create a New Table with the following formula:

 

Calendar = CALENDARAUTO()

This should create a calendar table that you can then relate to both of your other tables. Then you can create a slicer based upon this new Calendar table.

 

Then, you would have the following measures:

 

Revenue = SUM('Revenue'[NetRevenue])

Appeals = DISTINCTCOUNT('Appeals'[Appeal Id])

RevenuePerAppeal = DIVIDE([Revenue],[Appeals],0)

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

Proud to be a Datanaut!


jtownsend21 Member
Member

Re: Calculation between two unrelated data sources

That worked perfectly! I was trying to connect the date on the two tables, but hadn't thought about creating an independent calendar table. Thank you for your help on this!