Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
musafasih
Frequent Visitor

Filtering rows , calculating freight as a % of sales

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.

1 ACCEPTED SOLUTION
jameszhang0805
Resolver IV
Resolver IV

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

jameszhang0805_0-1614063104212.png

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#] )
    )

 

jameszhang0805_0-1614063733886.png

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

 

View solution in original post

4 REPLIES 4
jameszhang0805
Resolver IV
Resolver IV

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

jameszhang0805_0-1614063104212.png

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#] )
    )

 

jameszhang0805_0-1614063733886.png

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

 

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

amitchandak
Super User
Super User

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

its resolved, thanks!!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors