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 is recorded on the item level and as such, if an order has more than one line/row, the value is repeated.
To allow me to SUM the unique carriage cost per transaction I want to group by the 'Sales'[Transaction ID].
Additionally I have these filters

  •  'Sales'[Order Status] = "Invoiced" - To remove cancelled or incomplete orders.
  •  'Sales'[Order Carriage (GBP)] <> 0 - To remove the 300K rows without a carriage charge.

This is what I have come up with so far and it works.

 

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


My questions are as follows:

1) Is there a better (more compact/efficient) way to write my formula?

2) I thought it was bad practice to filter a fact table - How else could I do this?

  • 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

4 Replies

  • some_bih's avatar
    some_bih
    Community Champion

    Hi music43 try in Power Query in pbi filter out your filter (or the best do not "import" at all) your 2 filters criteria

    Sales'[Order Status]="Invoiced"

    'Sales'[Order Carriage (GBP)] <>0

    • music43's avatar
      music43
      Advocate II

      Thanks some_bih 

       

      Unfortunately, I can't remove them in PQ as I need the data for other parts of the report.

  • some_bih's avatar
    some_bih
    Community Champion

    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

  • Anonymous's avatar
    Anonymous
    Not applicable

    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