Forum Discussion

ratisaxena's avatar
ratisaxena
Regular Visitor
6 years ago

Adding two metrics based on multiple criterion

Hi, I'm very new to Power BI and any help on this would be appreciated.   I have two tables that I need to create a new measure. My first table is Revenue by Sales Rep by Product Type. My second...
Need Help
amitchandak's avatar
amitchandak
Super User
6 years ago

ratisaxena , try like sumx(summarize(filter(Table1, Table1[Sales Rep]=max(Table2[Sales Rep]) && Table1[Product Type]=max(Table2[Product Type])),Table1[Sales Rep],Table1[Product Type],
"_1",sum(Table1[Revenue])+sum(Table 2[Revenue])),[_1])

 

But better you have the common product type and Sales Rep dimesnion

ratisaxena's avatar
ratisaxena
Regular Visitor
6 years ago

Hi Amit,

 

I appreciate you taking the time out to help me out on this!

Can you please explain to me what the formula is doing?

 

To help you better understand what I'm trying to achieve here, a little more explanation on how my data is set up - 

 

My first table is the sales table which includes revenue by sales rep by category by account by date by etc etc..

My second table is a manual adjustment table (adjustments that we have had to make outside of the system generated report) which includes the manually adjusted revenue by sales rep by category. Not all reps have manual adjustments.

 

So I'm trying to calculate the following - total revenue by sales rep by category = table 1[revenue by category]+ table 2[manually adjusted revenue by category].