Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Recreating FIXED calculation from Tableau

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 ...
Need Help
johnt75's avatar
johnt75
Super User
3 years ago

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')

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

      • johnt75's avatar
        johnt75
        Super User
        3 years ago

        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.