How to sum up item types within the same invoice to find percentages in DAX

Question

Within an invoice, there are multiple items including a discount item. How would I find the % discount for the entire invoice, then use that to calculate the adjusted dollars for each item?

I can find the percentages for each invoice using summarize and sumx, but i cant get it to the item level.

This is what i have currently in my fact table:

Invoice Type        Item            Dollars
11111   Shipment    pancake         50
11111   Shipment    waffle          200
11111   Discount    discount item   20
11111   Discount    discount item   30

This is what i am looking to create:

item    dollars discount %  adjusted dollars
pancake 50      0.2         40
waffle  200     0.2         160

Here are the steps i am trying to achieve:

1. Add up the dollars for each Type
   • Shipment = 250
   • Discount = 50
2. Find the discount %
   • Discount / Shipment = 20%
3. Use that discount % to adjust the dollar amount for each item.
   • adjusted pancake = 50 * (1 - 0.2) = 40
   • adjusted waffle = 200 * (1 - 0.2) = 160
4. Add up those adjusted dollars in a measure.

The idea being that i could then put either items or invoices on columns in a pivot table and get the measure to give me an adjusted dollar amount. I was able to do it for just invoices, but i cannot figure out how to do it at the item level.

Any help would be appreciated.

Answer 1

Add the following measures

Shipment Amount:

Shipment Amount = 
    CALCULATE ( 
        SUM ( FactTable[Dollars] ),
        FactTable[Type] = "Shipment"
    )

Invoice Discount %:

Invoice Discount % = 
VAR InvoiceShipment = 
    CALCULATE ( 
        SUM ( FactTable[Dollars] ),
        FILTER ( 
            ALLEXCEPT ( FactTable, FactTable[Invoice] ), 
            FactTable[Type] = "Shipment"
        )
    )
VAR InvoiceDiscount = 
    CALCULATE ( 
        SUM ( FactTable[Dollars] ),
        FILTER ( 
            ALLEXCEPT ( FactTable, FactTable[Invoice] ), 
            FactTable[Type] = "Discount"
        )
    )
RETURN
    DIVIDE ( 
        InvoiceDiscount, 
        InvoiceShipment, 
        BLANK()
    )

Adjusted Amount:

Adjusted Amount = 
    SUMX ( 
        FactTable,
        [Shipment Amount] * ( 1 - [Invoice Discount %] )
    )

Now you can use these in your visualisation, as required.

See https://pwrbi.com/so_55602327/ for example PBIX file