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 relatio...
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

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