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

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