Forum Discussion

Thunders's avatar
Thunders
Frequent Visitor
2 years ago
Solved

DAX formula

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 quo...
  • FreemanZ's avatar
    FreemanZ
    2 years ago

    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's avatar
Thunders
Frequent Visitor
2 years ago

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's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    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
)