Forum Discussion
total Value with repeated rows
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 |
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.
4 Replies
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.
Hi,
These measures work
Min sale = MIN(Data[Total Sales])Measure = SUMX(VALUES(Data[Sales order ID]),[Min sale])Hope this helps.
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.
