Forum Discussion

music43's avatar
music43
Advocate II
2 years ago
Solved

Summarising

Hi   'Sales' is my fact table with approx 5.5M rows and the granularity is at the order item level. An order can have anywhere from 1 to 9999 lines/rows. The carriage/freight cost for each order i...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, music43 

    Thanks for some_bih reply. You can try the following measure(compact).

    Carriage =
SUMX(
    SUMMARIZE(
        FILTER(
            'Sales',
            'Sales'[Order Status] = "Invoiced"
            && 'Sales'[Order Carriage (GBP)] <> 0
        ),
        'Sales'[Transaction Id],
        'Sales'[Order Carriage (GBP)]
    ),
    'Sales'[Order Carriage (GBP)]
)

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know.     Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

some_bih's avatar
some_bih
Community Champion
2 years ago

Hi music43 

ok for limitations of your model.

The performance / optimisation is complex task including using  Performance analyzer, DAX Studio to see and check queries.

One of option, to investigate best performance is eventually using GROUPBY, SUMMARIZE or pattern ADDCOLUMNS(SUMMARIZE

Check link as example