topic Re: Calculating the total amount of sales without counting repeated order values in DAX Commands and Tips

Calculating the total amount of sales without counting repeated order values

Luzadriana255 — Wed, 11 Jan 2023 15:34:45 GMT

Hello,
I am trying to build a matrix of the total sales of different products as a function of time. In my excel list there are orders that appear more than once and I want to count the price of those orders only once. For example for toys in June I want to add only once 200 because it comes from the same order "Order 1".

My data look like the one in the following table:

Product code	Order	Date	Sales
Toys	1	06.06.2022	200
Toys	1	06.06.2022	200
Toys	2	12.06.2022	135
Clothes	3	09.06.2022	160
Clothes	4	23.06.2022	135
Clothes	4	23.06.2022	135
Toys	5	03.07.2022	50
Toys	5	03.07.2022	50
Toys	7	22.07.2022	20
Toys	8	03.07.2022	60
Clothes	6	09.07.2022	200
Clothes	9	12.07.2022	200
Clothes	10	12.07.2022	25

I want to develop a Dashboard matrix like the following:

Product/time	June	July
Toys	335	130
Clothes	295	425

However I am not sure what formula to use for this. Thank you for your help.

Re: Calculating the total amount of sales without counting repeated order values

Jihwan_Kim — Wed, 11 Jan 2023 15:42:41 GMT

Hi,

Please check the below picture and the attached pbix file.

Expected result measure: = SUMX ( SUMMARIZE ( Data, 'Product'[Product code], 'Calendar'[Date], Data[Order], Data[Sales] ), Data[Sales] )

Re: Calculating the total amount of sales without counting repeated order values

jewel_at — Wed, 11 Jan 2023 15:51:50 GMT

Hi Luzadriana255

You can also consider using the Remove Duplicates in Power Query, if you're not using that info in other visuals.

Select the Order, Date and Sales columns
Go to Home > Remove Row > Remove Duplicates

Jewel

Re: Calculating the total amount of sales without counting repeated order values

FreemanZ — Wed, 11 Jan 2023 16:25:53 GMT

hi Luzadriana255

you may also try to plot a table visual with two measures like this:

JUN = VAR _table = DISTINCT(TableName) RETURN SUMX( FILTER(_table, MONTH(TableName[Date]) =6), TableName[Sales] ) JUL = VAR _table = DISTINCT(TableName) RETURN SUMX( FILTER(_table, MONTH(TableName[Date]) =7), TableName[Sales] )

it worked like this:

Re: Calculating the total amount of sales without counting repeated order values

Luzadriana255 — Wed, 11 Jan 2023 16:25:54 GMT

Thank you so much!

Re: Calculating the total amount of sales without counting repeated order values

Luzadriana255 — Wed, 11 Jan 2023 16:26:25 GMT

Thank you so much!

Re: Calculating the total amount of sales without counting repeated order values

Luzadriana255 — Thu, 12 Jan 2023 09:46:27 GMT

Sounds good, however I have many different months and years, so it may take so much time to include every month. Thank you!