The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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! 🙂
Solved! Go to 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:
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:
Works perfect! Thank you so much! 🙂
Somehow still it is giving me hard time...se below.
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)
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
)
Thank you Dangar,
Just one little issue. The sales are the same for all products even thou I established relationship between tables.
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])))
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the July 2024 Power BI update to learn about new features.
User | Count |
---|---|
33 | |
28 | |
26 | |
25 | |
24 |
User | Count |
---|---|
50 | |
37 | |
26 | |
23 | |
21 |