Creating a Measure Based on 2 Tables

[ki]

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

[Anonymous]

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]

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

[Anonymous · Accepted solution]

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]

@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]

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