Recreating FIXED calculation from Tableau

[DiKar]

Hello,

I'm trying to recreate a calculation from Tableau that was giving the average Orders per User:
{ FIXED [UserID] : COUNTD([Order])}

I looked for possible syntax for Power BI, but have not been successful so far...here below some of my tries:

Av. count = CALCULATE(DISTINCTCOUNT('Table'[Order]), ALLEXCEPT('Table','Table'[UserID]))

Av. count = CALCULATE(COUNT('Table'[Order]), ALLEXCEPT('Table','Table'[UserID]))

[johnt75]

Try

Avg orders per customer =
AVERAGEX (
    VALUES ( 'Table'[User ID] ),
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Order] ) )
)

If you know you have only 1 row per order then you could replace the DISTINCTCOUNT with COUNTROWS('Table')

[DiKar]

it calculated for half an hour and gave an error can't show visual

[johnt75]

Do you have a user dimension ? Is there 1 row per order or can there be multiple rows per order ?

[DiKar]

There can be multiple, hence the need to calculate the average

[johnt75]

I understand that a user can have multiple orders, but can an order be split across multiple rows or does one row constitute an entire order? Doing a DISTINCTCOUNT of an order ID, for example, is a lot more time consuming than a simple COUNTROWS.

Also, if you have a user dimension then iterating over the values from that would be much quicker than having to scan the entire orders table to get the values.

[DiKar]

It is spread on multiple orders (2-7, many sometimes), so the total count of orders is not important, but how many orders average a user has is what I seek. Thanks

[johnt75]

Try

Avg orders per customer =
AVERAGEX ( VALUES ( 'Users'[User ID] ), CALCULATE ( COUNTROWS ( 'Orders' ) ) )

[DiKar]

this solution doesn't make distinct count 😕