topic Re: DAX formula in DAX Commands and Tips

DAX formula

Thunders — Tue, 28 Nov 2023 14:41:25 GMT

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! 🙂

Re: DAX formula

Dangar332 — Tue, 28 Nov 2023 15:51:19 GMT

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

Re: DAX formula

Thunders — Tue, 28 Nov 2023 16:35:56 GMT

Thank you Dangar,

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

Re: DAX formula

Dangar332 — Tue, 28 Nov 2023 16:55:51 GMT

HI, Thunders

use relation ship like below

Re: DAX formula

Thunders — Tue, 28 Nov 2023 17:18:32 GMT

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)

Re: DAX formula

Dangar332 — Tue, 28 Nov 2023 18:02:38 GMT

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 )

Re: DAX formula

Thunders — Tue, 28 Nov 2023 18:58:46 GMT

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

Re: DAX formula

FreemanZ — Wed, 29 Nov 2023 04:04:08 GMT

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:

Re: DAX formula

Thunders — Wed, 29 Nov 2023 13:43:53 GMT

Works perfect! Thank you so much! 🙂