Filtering rows , calculating freight as a % of sales

[musafasih]

Hi,

So here is my issue: I have one column for invoices, and another for transaction category. My invoice column consists of a number of invoices, for each invoice number, there could be 2 to 3 rows in the dataset, denoting different categories of transactions for the same invoice. The transaction category column consists of multiple categories, one of them being Freight costs. All invoices having Freight costs also have other transaction categories as well, but not all invoices have freight costs. I want to isolate the invoices having freight costs, but in such a way that not only the freight transactions are returned, but also other transactions associated with those invoices are returned, so that I am able to calculate freight as a percentage of sales only for invoices having freight. Thanks.

[jameszhang0805 · Accepted solution]

@musafasih  Not sure if this is the result you want
Virtual Data Source

Measure of Freight Invoice Amount

FreightInvoiceAmt = 
VAR _FreightInvoice =
    CALCULATETABLE (
        SUMMARIZE ( 'Table', 'Table'[Invoice#] ),
        'Table'[Category] = "Freight"
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        TREATAS ( _FreightInvoice, 'Table'[Invoice#] ),
        ALLEXCEPT ( 'Table', 'Table'[Invoice#] )
    )

Filtering rows , calculating freight as a % of sal....pbix 

[musafasih]

Thats exactly what I wanted! thanks alot!!!!! 😊

[amitchandak]

@musafasih ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[musafasih]

its resolved, thanks!!!