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

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