Calculating the total amount of sales without counting repeated order values

[Luzadriana255]

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.

[Jihwan_Kim · Accepted solution]

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

[FreemanZ]

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:

[Luzadriana255]

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

[jewel_at · Accepted solution]

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

[Luzadriana255]

Thank you so much!

[Jihwan_Kim · Accepted solution]

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

[Luzadriana255]

Thank you so much!