Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'm new in Power Bi and DAX world and come from Tableau.
I would like to create a column with DAX (not measure) wich give me the amount of sales for each customer/brand/year. I would like to create segmentation from this column, that's why I don't want a measure.
Here's my test model :
What i want is this (amount_customer_year) :
and if I remove "année", "product" and "brand" column, i keep the same granularity.
amount_customer_year_brand is a new attribut for my customers.
I solved this problem with a single table model
with the following formula :
amount_customer_year_brand = CALCULATE(
sum(Sales_full[amount]),
ALLEXCEPT(Sales_full,Sales_full[customer_code],Sales_full[Date].[Année],Sales_full[product_brand])
)
it works like a charm, but i can't make it works with my "3 tables model".
Can you help me making this formula works on my "3 tables schémas".
thank you in advance
Here's my test Data
Customer :
customer_code | customer_label |
1 | Customer_1 |
2 | Customer_2 |
Product
product_code | product_label | product_brand |
1 | product_1 | Brand_1 |
2 | product_2 | Brand_1 |
3 | product_3 | Brand_2 |
4 | product_4 | Brand_2 |
Sales
Sales_id | customer_code | product_code | date | amount |
1 | 1 | 1 | 01/01/2020 | 10 |
2 | 1 | 1 | 01/05/2020 | 10 |
3 | 1 | 2 | 01/06/2020 | 30 |
4 | 1 | 3 | 01/07/2020 | 100 |
5 | 1 | 4 | 01/08/2020 | 1000 |
6 | 1 | 4 | 01/09/2020 | 1000 |
7 | 1 | 3 | 01/07/2021 | 100 |
8 | 1 | 4 | 01/08/2021 | 1000 |
9 | 1 | 4 | 01/09/2021 | 1000 |
10 | 2 | 1 | 01/01/2020 | 20 |
11 | 2 | 2 | 01/05/2020 | 60 |
12 | 2 | 2 | 01/06/2020 | 60 |
13 | 2 | 2 | 01/07/2021 | 120 |
Sales_full
Sales_id | customer_code | product_code | Date | amount | Customer_label | product_label | product_brand |
1 | 1 | 1 | 01/01/2020 | 10 | Customer_1 | product_1 | Brand_1 |
2 | 1 | 1 | 01/05/2020 | 10 | Customer_1 | product_1 | Brand_1 |
3 | 1 | 2 | 01/06/2020 | 30 | Customer_1 | product_2 | Brand_1 |
4 | 1 | 3 | 01/07/2020 | 100 | Customer_1 | product_3 | Brand_2 |
5 | 1 | 4 | 01/08/2020 | 1000 | Customer_1 | product_4 | Brand_2 |
6 | 1 | 4 | 01/09/2020 | 1000 | Customer_1 | product_4 | Brand_2 |
7 | 1 | 3 | 01/07/2021 | 100 | Customer_1 | product_3 | Brand_2 |
8 | 1 | 4 | 01/08/2021 | 1000 | Customer_1 | product_4 | Brand_2 |
9 | 1 | 4 | 01/09/2021 | 1000 | Customer_1 | product_4 | Brand_2 |
10 | 2 | 1 | 01/01/2020 | 20 | Customer_2 | product_1 | Brand_1 |
11 | 2 | 2 | 01/05/2020 | 60 | Customer_2 | product_2 | Brand_1 |
12 | 2 | 2 | 01/06/2020 | 60 | Customer_2 | product_2 | Brand_1 |
13 | 2 | 2 | 01/07/2021 | 120 | Customer_2 | product_2 | Brand_1 |
Thank you again for reading this far
Is this the expected result?
Thank you for your anwser, but unfortunatly, no, the customer_1 should have 3 values even if i don't display brand_product or "Année"(year).
in fact there is a mistake in my original post, there should have 3 values not 2, so my formula doesn't work for my one table model :'(
Something's not adding up (really!)
I've built the pbix but I don't see the same results as you show.
Is the picture with customer_label and the amount_customer_year_brand missing a column? Because powerbi would merge those rows otherwise.
I also don't get the results from the first visual with the amount_customer_year_brand column
Perhaps you could link your pbix (you need to use a 3rd party site) and I'll take a look
Thank you for your reply.
Here my two pbix files, hope this will work.
https://1drv.ms/u/s!Ar16OoDci-UGhNhXGDJ6Uk5_E5NauQ?e=bBmQGE
Sry for delay
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |