topic Re: Filtering rows , calculating freight as a % of sales in DAX Commands and Tips

Filtering rows , calculating freight as a % of sales

musafasih — Tue, 23 Feb 2021 05:55:39 GMT

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.

Re: Filtering rows , calculating freight as a % of sales

amitchandak — Tue, 23 Feb 2021 06:32:06 GMT

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

Re: Filtering rows , calculating freight as a % of sales

jameszhang0805 — Tue, 23 Feb 2021 07:03:20 GMT

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

Re: Filtering rows , calculating freight as a % of sales

musafasih — Tue, 23 Feb 2021 21:41:19 GMT

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

Re: Filtering rows , calculating freight as a % of sales

musafasih — Tue, 23 Feb 2021 21:41:43 GMT

its resolved, thanks!!!