Count number of orders per customer in column while several rows per order exist

[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:

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

other nice things to further analyze would be:

1. 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

[Quds]

A bit too late  

Create  a calculated column with this formula

Number of orders per customer = COUNTX (

FILTER ( 'sample'; EARLIER ( 'sample'[customer_id] ) = 'sample'[customer_id]);

'sample'[customer_id]

)

[Anonymous]

Hi @undecided,

>>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.

Power bi not support to create calculate column/table based on filter or slicer, you need to use measure to instead.

You can refer to use below measure to get distinct order count per customer.

Distinct Order Per Customer =
CALCULATE (
    DISTINCTCOUNT ( 'sample'[BestellNr.] ),
    FILTER (
        ALLSELECTED ( 'sample' ),
        'sample'[Customer ID] = SELECTEDVALUE ( 'sample'[Customer ID] )
    )
)

>> what's the order value per order/customer

Can you please explain more about this requirement? I'm not so sure how to deal with the filtered records.

Regards,
Xiaoxin Sheng