[]
v8.0
(Showing Draft Content)

Use Case 1 - 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]
        )
    )
)