total Value with repeated rows

[randyvdr]

Hello Communitiy,

Would you be so gentle to help me with the following query.

I have the next example set of data:

Region	Country	Category ID	Sales order ID	Total Sales
EUROPE	Italy	A	E10	$                    100.00
EUROPE	Italy	A	E10	$                    100.00
EUROPE	Italy	B
EUROPE	Germany	C	E20	$                    150.00
EUROPE	Norway	D	E30	$                    200.00
EUROPE	Norway	E	E30	$                    200.00
ASIA	Thailand	D	AS10	$                    300.00
ASIA	Thailand	E	AS10	$                    300.00
ASIA	Japan	G	AS20	$                    275.00
ASIA	Japan	H	AS30	$                    225.00
ASIA	Korea	I	AS40	$                    215.00
ASIA	Korea	I	AS40	$                    215.00
AFRICA	Nigeria	K
AFRICA	Nigeria	K
AFRICA	Angola	L	AF10	$                    520.00
AFRICA	Kenya	M	AF20	$                    512.00

As you can see I have repeated rows of Total Sales, my output should be as following:

Sales Order ID	Total_sales_calc
E10	$           100.00
E20	$           150.00
E30	$           200.00
AS10	$           300.00
AS20	$           275.00
AS30	$           225.00
AS40	$           215.00
AF10	$           520.00
AF20	$           512.00
Total	$        2,497.00

[randyvdr]

Hello community, thanks for all your fast reponses, the results from all three formulas are the following:

@vicky_  & @Anonymous  the answers are as expected

[Anonymous]

Hi @ randyvdr,

Thanks to the two Super Users @Ashish_Mathur , @vicky_ for their responses.

You can also try the following DAX to create the Total_sales_calc measure.

Total_sales_calc = 
SUMX(
    DISTINCT('Sales'[Sales order ID]),
    CALCULATE(MAX('Sales'[Total Sales]))
)

Best Regards,

Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

These measures work

Min sale = MIN(Data[Total Sales])

Measure = SUMX(VALUES(Data[Sales order ID]),[Min sale])

Hope this helps.

[vicky_ · Accepted solution]

Hi, 

Please try the following:

Sales calc = SUMX(SUMMARIZE('Table', 'Table'[Sales order ID], 'Table'[Total Sales]), 'Table'[Total Sales])

Note - for this to work, i assume that each combination of sales order ID and total sales is unique. If you end up with a row that's like:

Region	Country	Category ID	Sales order ID	Total Sales
EUROPE	Italy	A	E10	$                    100.00
EUROPE	Italy	A	E10	$                    200.00

then the result in your table will show $300. If this is not the result that you expect, then you will need to provide more details on how you expect to handle this case.