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])))
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
20 | |
18 | |
17 | |
17 |
User | Count |
---|---|
34 | |
21 | |
19 | |
18 | |
10 |