DAX formula

[Thunders]

Hello team,

I have following sititation:

I have 2 tables, I need to create 3rd table.

Using DAX summarize function I get product unique values (see required Output), but I'm strugling to bring quotas, sales and calclate attainments. Example below: 

Any help appreciated!

Thank you! 🙂

[FreemanZ · Accepted solution]

hi @Thunders ,

try to:

1) add a calculated product table like:

Product = VALUES(sales[Product])

2) relate product[product] with sales[product] and quotas[product]

3) plot a table visual with product[product] and three measures like:

@sales = SUM(sales[Sales])
@Quotas = SUM(quotas[Quotas])
Attainment% = DIVIDE([@sales], [@Quotas]) 

it worked like:

[Thunders]

Works perfect! Thank you so much! 🙂

[Thunders]

Somehow still it is giving me hard time...se below.

[Thunders]

Since I can't established relationship for products between Quotas and Sales table I created new table with unique product values. Works well for Quotas/Sales, but not for table 3 (no idea why)

[Dangar332]

hi, @Thunders 

try below

Table 3 = 
var a = CALCULATE(sum(quotas[quotas]))
var b = CALCULATE(SUM(sales[sales]),CROSSFILTER(sales[product],product_type[product],4))
return
ADDCOLUMNS(
      SUMMARIZE(
        quotas,
        quotas[product]
      ),
      "quotas",a
,"sales",b,
"attainments",b/a
)

[Thunders]

Thank you Dangar,

Just one little issue. The sales are the same for all products even thou I established relationship between tables.

[Dangar332]

HI, @Thunders 

use relation ship like below 

[Dangar332]

hi, @Thunders 

try below code

Table 3 = 
ADDCOLUMNS(
      SUMMARIZE(
        quotas,
        quotas[product]
      ),
      "r",CALCULATE(sum(quotas[quotas]))
,"sales",CALCULATE(SUM(sales[sales])),
"attainments",CALCULATE(SUM(sales[sales]))/CALCULATE(sum(quotas[quotas])))