cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

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

1 ACCEPTED SOLUTION
Super User

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 8
Super User

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:

Frequent Visitor

Works perfect! Thank you so much! 🙂

Frequent Visitor

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

Frequent Visitor

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)

Memorable Member

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
SUMMARIZE(
quotas,
quotas[product]
),
"quotas",a
,"sales",b,
"attainments",b/a
)``````
Frequent Visitor

Thank you Dangar,

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

Memorable Member

HI, @Thunders

use relation ship like below

Memorable Member

hi, @Thunders

try below code

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors