cancel
Showing results for
Did you mean:
Frequent Visitor

## Need help to calculate aggregate column based on mesure value

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".

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

4 REPLIES 4
Resolver IV

Is this the expected result?

Frequent Visitor

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

Super User II

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

Frequent Visitor

Here my two pbix files, hope this will work.

https://1drv.ms/u/s!Ar16OoDci-UGhNhXGDJ6Uk5_E5NauQ?e=bBmQGE

Sry for delay

Announcements