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 quotas, sales and calclate attainments. Example below: 

Any help appreciated!

Thank you! 🙂

  • 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:

     

     

8 Replies

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

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

     

     

  • Thunders's avatar
    Thunders
    Frequent Visitor
    2 years ago

    Thank you Dangar,

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

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

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

     

  • FreemanZ's avatar
    FreemanZ
    Super User
    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

      Works perfect! Thank you so much! 🙂