Actual Sales Profit

Case 1: Actual Sale Profit

Requirement

In this case, we will calculate the actual sales profit using the category name as a dimension. The formula for calculating the actual sales profit is:

(SaleAmount*(1-Discount)-UnitPrice)*Quantity

Steps for creating a dashboard

1.Drag a Column chart and drag the English Product category name to Axis.

drag-category

2. Add a calculated column on Table 'FactResellerSales'.

add-calculated-column

3. Input the expression given below and save it as SaleProfit. So the name of the Calculated Column will be SaleProfit.

('FactResellerSales'[SalesAmount]*(1-'DimPromotion'[DiscountPct])-'FactResellerSales'[UnitPrice])*'FactResellerSales'[OrderQuantity]

saleprofit-expression

4. Drag the calculated column SaleProfit to the Values. Aggregation Method chooses sum.

sumofsailprofit

Explanation

('FactResellerSales'[SalesAmount]*(1-'DimPromotion'[DiscountPct])-'FactResellerSales'[UnitPrice])*'FactResellerSales'[OrderQuantity]

The calculated column above is a special column. We can use it as a normal column. It can be dragged to the dimension, values, filters, sort, etc.

The whole WAX is below for this dashboard request.

EVALUATE
SUMMARIZE (
    CALCULATETABLE (
        SELECTATTRIBUTES ( 'DimProductCategory'[EnglishProductCategoryName] )
    ),
    'DimProductCategory'[EnglishProductCategoryName],
    "FactResellerSales_SaleProfit_SUMX",
        CALCULATE (
            SUMX (
                CALCULATETABLE (
                    ADDCOLUMNS (
                        SELECTATTRIBUTES (
                            FactResellerSales,
                            'DimProductCategory'[EnglishProductCategoryName]
                        ),
                        "FactResellerSales_SaleProfit",
                            ( ( ( 'FactResellerSales'[SalesAmount] - ( 1 - 'DimPromotion'[DiscountPct] ) ) - 'FactResellerSales'[UnitPrice] ) * 'FactResellerSales'[OrderQuantity] )
                    )
                ),
                [FactResellerSales_SaleProfit]
            )
        )
)