Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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 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
Thats exactly what I wanted! thanks alot!!!!! 😊
@musafasih ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
its resolved, thanks!!!
User | Count |
---|---|
40 | |
26 | |
22 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |