cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sam07
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 :

 

image.png

 

 

 

 

 

 

 

 

 

What i want is this (amount_customer_year) : 

image.png

 

 

 

 

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.

image.png

 

 

 

 

I solved this problem with a single table model 

image.png

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_codecustomer_label
1Customer_1
2Customer_2

 

Product

product_codeproduct_labelproduct_brand
1product_1Brand_1
2product_2Brand_1
3product_3Brand_2
4product_4Brand_2

 

Sales

Sales_idcustomer_codeproduct_codedateamount
11101/01/202010
21101/05/202010
31201/06/202030
41301/07/2020100
51401/08/20201000
61401/09/20201000
71301/07/2021100
81401/08/20211000
91401/09/20211000
102101/01/202020
112201/05/202060
122201/06/202060
132201/07/2021120

 

Sales_full

Sales_idcustomer_codeproduct_codeDateamountCustomer_labelproduct_labelproduct_brand
11101/01/202010Customer_1product_1Brand_1
21101/05/202010Customer_1product_1Brand_1
31201/06/202030Customer_1product_2Brand_1
41301/07/2020100Customer_1product_3Brand_2
51401/08/20201000Customer_1product_4Brand_2
61401/09/20201000Customer_1product_4Brand_2
71301/07/2021100Customer_1product_3Brand_2
81401/08/20211000Customer_1product_4Brand_2
91401/09/20211000Customer_1product_4Brand_2
102101/01/202020Customer_2product_1Brand_1
112201/05/202060Customer_2product_2Brand_1
122201/06/202060Customer_2product_2Brand_1
132201/07/2021120Customer_2product_2Brand_1

 

Thank you again for reading this far

4 REPLIES 4
jameszhang0805
Resolver IV
Resolver IV

Is this the expected result?

jameszhang0805_0-1614246234875.png

jameszhang0805_2-1614246758486.png

 

 

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

 

Sam07_0-1614258493039.png

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
Super User II
Super User II

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

Sam07
Frequent Visitor

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors