Need help to calculate aggregate column based on mesure value

[Anonymous]

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

[jameszhang0805]

Is this the expected result?

[Anonymous]

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 :'(

[HotChilli]

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

[Anonymous]

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