DAX measure to calculate aggregate using a sub-level characteristic

[sanimesa]

I have a sales history table with Order Numbers, Order Amount, Std Cost per Order, SKU Category and SKU. Each SKU Category has multiple SKUs. There are multiple line items for each SKU. 

I need to build a summary table for SKU categories with gross sales, number of SKUs within each category, total gross profit % etc. Then, I have to add a column called Gross Profit Opportunity to be calculated as follows:

1. Calculate the Gross profit % for each SKU

2. For each SKU, if the Gross profit % is less than a target profit %, then multiply the Gross total sales for that SKU with the difference between the SKU Gross profit % and Target profit % - ergo if target profit % is 50% and Gross Profit % for that SKU is 40%, then multiply the total sales for the SKU with the 10% difference.

3. Sum the values for each SKU obtained in 2 for each SKU category

I tried using ALLEXCEPT and measures to get this, but getting stuck. Please help. 

[Zubair_Muhammad]

Hi @sanimesa,

Try this. Go to modelling tab and select new table

See the attached file as well

New Table =
ADDCOLUMNS (
    SUMMARIZE (
        Table1,
        Table1[SKU Category],
        "Gross Sales", SUM ( Table1[Sales Amount] ),
        "Number of SKUs", COUNT ( Table1[SKU] ),
        "Total Gross Profit", SUM ( Table1[Sales Amount] ) - SUM ( Table1[Std Cost] )
    ),
    "Gross Profit Opportunity", SUMX (
        FILTER ( table1, Table1[SKU Category] = EARLIER ( Table1[SKU Category] ) ),
        IF (
            ( Table1[Sales Amount] - Table1[Std Cost] )
                < Table1[Target profit],
            Table1[Sales Amount]
                * ( Table1[Target profit]
                - ( Table1[Sales Amount] - Table1[Std Cost] ) )
        )
    )
)

[sanimesa]

Thanks for your reply, however it seems the target numbers are too high. I have slightly edited the post to indicate the calculates are at percentage, not absolute profit. Also, the overall target profit percent is same across the board.

This is what I have done. 

Added a measure for SKU Gross profit margin:

SKU Gross Margin % = CALCULATE(1 - DIVIDE(SUM('Sales History'[Product Std Cost]), SUM('Sales History'[Product Sales $ ])), ALLEXCEPT('Sales History', 'Sales History'[Item Number]))

Added a column for the profit opportunity: 

SKU Profit Oppy = IF([SKU Gross Margin %] < 0.55, 'Sales History'[Product Sales $ ]*(0.55 - [SKU Gross Margin %]), 0) 

Now when I add the SKU Category and SKU Gross Profit Opportunity to a table, it does appear to show a close enough value. I am not quite sure this is the most efficient way to do this. 

[Zubair_Muhammad · Accepted solution]

@sanimesa

Try this.

Could you paste your data and expecetd result in table format in the post?

Also you can share your file via onedrive and/or google drive

New Table =
ADDCOLUMNS (
    SUMMARIZE (
        Table1,
        Table1[SKU Category],
        "Gross Sales", SUM ( Table1[Sales Amount] ),
        "Number of SKUs", COUNT ( Table1[SKU] ),
        "Total Gross Profit", SUM ( Table1[Sales Amount] ) - SUM ( Table1[Std Cost] )
    ),
    "Gross Profit Opportunity", SUMX (
        FILTER ( table1, Table1[SKU Category] = EARLIER ( Table1[SKU Category] ) ),
        IF (
            ( ( Table1[Sales Amount] - Table1[Std Cost] )
                / Table1[Sales Amount] )
                < .55,
            Table1[Sales Amount]
                * (
                    0.55
                        - DIVIDE ( Table1[Sales Amount] - Table1[Std Cost], Table1[Sales Amount] )
                )
        )
    )
)