topic Re: Total order count of the customer who buy a brand at least one time in DAX Commands and Tips

Total order count of the customer who buy a brand at least one time

ronald0 — Fri, 12 Jul 2024 12:26:13 GMT

Hi,

I need to get the total order of the customers who buy a brand at least one time.

Es :

I have table with BRAND, and for each brand the CustomerID who buy that brand,

i want the sum of the total orders made the customer who buy a brand, the Total Customers Orders

i try with :

total customer order = CALCULATE(SUMX(SUMMARIZE(Sales,Sales[KeyOrdine]),1),Sales[FlagAnnullato]=0,ALL(Dim_Brand[Brand]))

but in the aggregate sum it doesn't work.

Thanks

Re: Total order count of the customer who buy a brand at least one time

BeaBF — Fri, 12 Jul 2024 12:52:25 GMT

ronald0 Hi! Can you paste some sample data?and explain better the desidered output?

BBF

Re: Total order count of the customer who buy a brand at least one time

Greg_Deckler — Fri, 12 Jul 2024 12:56:27 GMT

ronald0 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

To get more specific, I agree with BeaBF , sample data always expedites a solution.

Re: Total order count of the customer who buy a brand at least one time

ronald0 — Fri, 12 Jul 2024 14:03:31 GMT

Thanks for the support.

Yes, the problem is the total sum of the measure is wrong.

i have a sales fact table and the brand table.

Now i have the numbers of the total customer who buy that Brand,

Customer = CALCULATE(SUMX ( SUMMARIZE ( Sales, Sales[IdFidelity] ), 1) )

and the number of the orders of that Brand

Orders = CALCULATE(SUMX(SUMMARIZE(Sales,Sales[KeyOrder]),1))

Now, i want calculate the number of Total orders made by the customers who buy that brand.

BRAND	Customers	Orders	Total Customer Orders
Adidas	200	100	150

Mean, now i know that 200 customers buy adidas , with 100 order, but i want to know , that 200 customers how many orders made in total ? ( not only Adidas).

I have this number for each customer, but the total sum of the measure is wrong.

the total sum of the measure 'total customer order' it would be 95 in this example. 696049 is the sum of all the orders.

Re: Total order count of the customer who buy a brand at least one time

ronald0 — Mon, 15 Jul 2024 08:39:53 GMT

I have the sales fact table like this :

IdCustomer	OrderKey	Product	product code	Brand
0025	12256	product_x	s14545	brand1
0025	12256	product_y	s14546	brand2
0026	13254	product_z	s14541	brand3
0027	13242	product_y	s14546	brand2
0025	15529	product_x	s14545	brand1
0025	15529	product_h	s14549	brand4
0025	15529	product_y	s14546	brand2
0027	17546	product_h	s14549	brand4
0027	17546	product_g	s14544	brand5
0025	12250	product_x	s14545	brand1
0027	12349	product_y	s14546	brand2
0025	12506	product_z	s14541	brand3

The result i want is : Total Customer Order , the total count of all order by customer , who buy that brand at least one time, not the total order only for that brand ( the column Orders)

Customer by brand	Orders	Total Customer Orders	Result i have Now
brand1	3	4	8
0025	3	4	4
brand2	4	7	8
0025	2	4	4
0027	2	3	3
brand3	2	5	8
0025	1	4	4
0026	1	1	1
brand4	2	7	8
0025	1	4	4
0027	1	3	3
brand5	1	3	8
0027	1	3	3
Total Order	8	8	8

the measure ihave now is ( wrong in the total ) :

Measure = CALCULATE(SUMX(SUMMARIZE(Sales,Sales[OrderKey ]),1),ALL(Dim_Brand[Brand]))

can anyone help?

Thanks

Re: Total order count of the customer who buy a brand at least one time

ronald0 — Mon, 15 Jul 2024 10:17:56 GMT

This is the sample sales fact table :

This the result i want :

I want to calculate the Total Customers Orders , the total count of all orders by customers, who buy that brand ( in the filter context) at least one time. Not the total orders by the customer for only that brand ( column Orders)

I write this measure : but it's wrong in the grand total, correct for single idfidelity. ( due to the ALL function)

CALCULATE(SUMX(SUMMARIZE(Sales,Sales[OrdersKey]),1),ALL(Dim_Brand[Brand])

can anyone help? Thanks,

Re: Total order count of the customer who buy a brand at least one time

Anonymous — Tue, 16 Jul 2024 06:40:28 GMT

Hi ronald0 ,

Please create another new measure as below and put it on the visual to replace the original measure [Total Customer Order]:

New Measure = SUMX ( VALUES ( Dim_Brand[Brand] ), [Total Customer Order] )

In addition, you can refer the following links to try to solve your problem...

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

If the above ones can't help you figure out, it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards