Forum Discussion
Distinct text
I want to see the revenue by product. I have created this table.
I removed the amounts, but they differ. Now i want the total amount by product. As you can see there are a lot of duplicates in the Item Description column. The relationships look like this:
Eventualy i would like to have a bar chart. But for now a column is fine.
Hi zoemostert,
As each product item can be involved in different order ID, absolutely, it will show a lot of same product per order ID when you add both of these two fields into a table visual. It doesn't make sense to remove duplicates, only to display unique product item. Unless you remove the Order ID field from table visual.
To show the revenue by each product, you could try below solution.
Create a summarized table.
Summarize Order details = SUMMARIZE ( 'Order details', 'Order details'[OrderID], "Item Description", FIRSTNONBLANK ( 'Order details'[Item description], 1 ) )
In 'Total Invoiced', add a calculated column to list product name.
Item = LOOKUPVALUE ( 'Summarize Order details'[Item Description], 'Summarize Order details'[OrderID], 'Total invoiced'[OrderID] )
Create two measures:
Aantal_producten = CALCULATE ( COUNTA ( 'Order details'[Item description] ), FILTER ( 'Order details', 'Order details'[OrderID] = MAX ( 'Total invoiced'[OrderID] ) ) ) Total invoiced per Item = CALCULATE ( SUM ( 'Total invoiced'[Total invoiced] ), ALLEXCEPT ( 'Total invoiced', 'Total invoiced'[Item] ) )
Then, drag relative columns from 'Total invoiced' and above two measures into table visual.
The above solution applies to the scenario where each Order ID contains only one product type. If the relationship between OrderID and Item description is many to many, your requirement cannot be achieved according to current information.
Besides, I have attached the .pbix file for your reference.
Best regards,
Yuliana Gu
5 Replies
Hi zoemostert,
What kind of issue/challenge you're facing now?
I'm sorry. I didn't get your problem.
I want to see the revenue by each product. Now i see the revenue per order ID. And a lot of the same products.
Hi zoemostert,
As each product item can be involved in different order ID, absolutely, it will show a lot of same product per order ID when you add both of these two fields into a table visual. It doesn't make sense to remove duplicates, only to display unique product item. Unless you remove the Order ID field from table visual.
To show the revenue by each product, you could try below solution.
Create a summarized table.
Summarize Order details = SUMMARIZE ( 'Order details', 'Order details'[OrderID], "Item Description", FIRSTNONBLANK ( 'Order details'[Item description], 1 ) )
In 'Total Invoiced', add a calculated column to list product name.
Item = LOOKUPVALUE ( 'Summarize Order details'[Item Description], 'Summarize Order details'[OrderID], 'Total invoiced'[OrderID] )
Create two measures:
Aantal_producten = CALCULATE ( COUNTA ( 'Order details'[Item description] ), FILTER ( 'Order details', 'Order details'[OrderID] = MAX ( 'Total invoiced'[OrderID] ) ) ) Total invoiced per Item = CALCULATE ( SUM ( 'Total invoiced'[Total invoiced] ), ALLEXCEPT ( 'Total invoiced', 'Total invoiced'[Item] ) )
Then, drag relative columns from 'Total invoiced' and above two measures into table visual.
The above solution applies to the scenario where each Order ID contains only one product type. If the relationship between OrderID and Item description is many to many, your requirement cannot be achieved according to current information.
Besides, I have attached the .pbix file for your reference.
Best regards,
Yuliana Gu
Hi, you created a many-to-many relationship between "total invoiced" and "order details" using a bridge table "orders. That is correct this way. Now just apply the routine as described in https://www.sqlbi.com/articles/many-to-many-relationships-in-power-bi-and-excel-2016/
Best regards
Alexander
