Forum Discussion

ki's avatar
ki
Frequent Visitor
5 years ago
Solved

Creating a Measure Based on 2 Tables

Hello,

 

Trying to create a measure that shows revenue for specific product category as percentage of total revenue. Below are the 2 tales. This is of course easily achievable by creating a relationship b/n those and then creating a visual. Yet, I need it is a measure 😞 Want to avoid long coding as the real data is bigger/complex (e.g. I can combine filter and sumx in Table 1 given that I know the category of each product) and want to avoid joining tables.

 

Category A = 43% of Total Revenue

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi ki ,

     

    So category A should be 12/(12+6+23+12+45+12) = 10.9%? If so, you will need to modify the measure as below.

    Measure = 
var total_rev = CALCULATE(SUM(Table1[revenue]),ALL(Table1))
var pro_rev = CALCULATE(SUM(Table1[revenue]),FILTER(ALL(Table1),Table1[product] in VALUES(Table2[product])))
return
pro_rev/total_rev

     

    Best Regards,

    Jay

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    ki , assume revenue is a measure , both tables are joined  on product and you are using product and product category from product table in visual

     

    calculate(divide([revenue], calculate([revenue], filter(allselected(product), product[product category] = max(product[product category] )))))

    • ki's avatar
      ki
      Frequent Visitor
      5 years ago

      Wow, that was fast! But I am not sure I get it... The divide function is clear. What I dont' get is the second calculate - this pretty much should filter on a specific category. How would it look for let's say building this measure for Category A? I tried different syntax, but I get different errors...

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi ki ,

     

    Check the measure.

    Measure = 
var avg_rev = CALCULATE(SUM(Table1[revenue]),ALL(Table1))/CALCULATE(DISTINCTCOUNT(Table2[category]),ALL(Table2))
var pro_rev = CALCULATE(SUM(Table1[revenue]),FILTER(ALL(Table1),Table1[product] in VALUES(Table2[product])))
return
pro_rev/avg_rev

    You said "Category A = 43% of Total Revenue", I assumed that you mean avg of revenue? Otherwise I don't know how to get 43% for Category A.

     

    Best Regards,

    Jay

    • ki's avatar
      ki
      Frequent Visitor
      5 years ago

      Thanks, the 43% wasj ust an example of what I want to get.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi ki ,

         

        So category A should be 12/(12+6+23+12+45+12) = 10.9%? If so, you will need to modify the measure as below.

        Measure = 
var total_rev = CALCULATE(SUM(Table1[revenue]),ALL(Table1))
var pro_rev = CALCULATE(SUM(Table1[revenue]),FILTER(ALL(Table1),Table1[product] in VALUES(Table2[product])))
return
pro_rev/total_rev

         

        Best Regards,

        Jay