Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
87 | |
87 | |
52 | |
37 | |
23 |