cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Distinct text

I want to see the revenue by product. I have created this table.help.png

 

 

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:help2.png

 

 

Eventualy i would like to have a bar chart. But for now a column is fine. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Distinct text

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.

1.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Highlighted
Solution Sage
Solution Sage

Re: Distinct text

Hi @zoemostert,

 

What kind of issue/challenge you're facing now?

 

I'm sorry. I didn't get your problem. 

Highlighted
Helper I
Helper I

Re: Distinct text

@SivaMani

I want to see the revenue by each product. Now i see the revenue per order ID. And a lot of the same products. 

Highlighted
Frequent Visitor

Re: Distinct text

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

Highlighted
Microsoft
Microsoft

Re: Distinct text

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.

1.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper I
Helper I

Re: Distinct text

Yuliana you're the best! After step two I allready had the desired result. Thank u so much. 

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors