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
Helper II
Helper II

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
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors