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 table is also Revenue by Sales Rep by Product Type.

 

I want to be able to do the following - 

New Measure = Table1[Revenue] + Table 2[Revenue]

when Table1[Sales Rep]=Table2[Sales Rep]

          Table1[Product Type]=Table2[Product Type]

 

Thank you for your help. 

Need Help

5 Replies

    • ratisaxena's avatar
      ratisaxena
      Regular Visitor
      6 years ago

      Hi Greg,

       

      Thank you for sharing that link with me - it was a super helpful read. 

      I apologize for laying out my problem so ambiguously.

       

      To answer your question, I'm trying to calculate the total revenue by sales rep by category.

      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 my total revenue by sales rep by category = table 1[revenue by category]+ table 2[manually adjusted revenue by category].

       

       

  • 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].

       

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    6 years ago

    Hi ratisaxena ,

     

    Can you share the sample data based on your table model? So that we can know your scenario more clearly.

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.