count order per customer when list of items in order with multiple order numbers in row

[undecided]

Hi, I have a table with order information with each row representing one item in the order. Therefore one order can consist of several rows. information I have is

    SKU
    Order Number
    customer ID
    date/year
    price


Sample Data


SKU  BestellNr.    Customer ID    Date                 Price

123    1001             sad@w.de    17/02/2017       25

124    1002             af@w.de       25/02/2018       14

128    1002             af@w.de       25/02/2018       9

136    1002             af@w.de       25/02/2018       68

198    1003             pf@w.de       02/05/2018      47


Now I want to extract the following infos from the table to analyze them:


    how many orders (not items) has each customer made in all the time

other nice things to further analyze would be:

    what's the order value per order/customer

How would I go about this now? My idea was to create a new table in the table view which contains data aggregated by the order number and add columns for customer id and year to this table... I got one column in the new table using

DISTINCT(Tabelle1[Bestellnr. - Text])

but was not able to add other information to this table then.

When trying FILTER it tells me it is getting multiple values where one is expected.


Or would I have to use measures instead? My reason for choosing a table was that I wanted to be able to understand the data better by seeing the information...


Would be greatful for hints on how to progress as I am new to power bi and spent a whole day trying to figure it out 😉

Thanks

Marcus

[Phil_Seamark]

Hi @undecided

There are a few things you can do in DAX.  To see a disinct count of orders by customer you could create the following calculated measure and add it to a visual that has your [Customer ID] column, such as grid or matrix.

Count of Customer Orders = 
    DISTINCTCOUNT('Tabelle1'[BestellNr.])

To get a table with a sum of prices for Customer/Order you could try adding this calculated table.

Customer Orders = 
	SUMMARIZECOLUMNS(
			'Tabelle1'[BestellNr.] ,
			'Tabelle1'[Customer ID] , 
			"Sum of Price" , SUM(Tabelle1[Price])
			)