Use Case 3 - Categorize fields based on calculations

Case 3: Categorize fields based on calculations

Requirement

Add a new category field based on the calculation results.

For example, if the sales amount < 1000000, the field value is 'good', if 12000000>sales amount >=1000000, the field value is 'better', if the sales amount >=12000000, the field value is 'best'.

Steps for creating a dashboard

1. Add a pivot table and bind fields in rows and values. Here, we will drag the English category name to Rows.

Drag Category

2. From the FactResellerSales table drag SaleAmount to Values.

Drag SalesAmount

3. Add a Measure on Table 'FactResellerSales'.

Add Measure on FactResellersSales

4. Input the expression given below and save it as MeasureCustomCategoryName. So, the name of the Measure will be MeasureCustomCategoryName.

switch(true,
       sum('FactResellerSales'[SalesAmount]) < 1000000,"good",
       sum('FactResellerSales'[SalesAmount])>=1000000 && sum('FactResellerSales'[SalesAmount]) <12000000 ,"better",
       sum('FactResellerSales'[SalesAmount]) >=12000000,"best")

MeasureCustomCategoryName Expression

5. Drag the measure MeasureCustomCategoryName above to Values.

Drag MeasureCustomCategoryName

Explanation

switch(true,
    sum('FactResellerSales'[SalesAmount]) < 1000000, "good",
    sum('FactResellerSales'[SalesAmount])>=1000000 && sum('FactResellerSales'[SalesAmount]) <12000000 , "better",
    sum('FactResellerSales'[SalesAmount]) >=12000000, "best")

The WAX above is a measure. This uses the Switch function to implement this case, or you can use If function to do the same thing.

The full WAX expression is below:

EVALUATE
SELECTCOLUMNS(
    SUMMARIZE(
        CALCULATETABLE(
            SELECTATTRIBUTES(
                'DimProductCategory'[EnglishProductCategoryName]
            )
        ),
        'DimProductCategory'[EnglishProductCategoryName],
        "FactResellerSales_ MeasureCustomCategoryName _SUMX",
        CALCULATE(
            switch(
                True,
                (
                    sum(
                        'FactResellerSales'[SalesAmount]
                    )
                    <
                    1000000
                ),
                "good",
                (
                    (
                        sum(
                            'FactResellerSales'[SalesAmount]
                        )
                        >=
                        1000000
                    )
                    &&
                    (
                        sum(
                            'FactResellerSales'[SalesAmount]
                        )
                        <
                        12000000
                    )
                ),
                "better",
                (
                    sum(
                        'FactResellerSales'[SalesAmount]
                    )
                    >=
                    12000000
                ),
                "best"
            )
        ),
        "FactResellerSales_SalesAmount_SUMX",
        CALCULATE(
            SUMX(
                CALCULATETABLE(
                    SELECTATTRIBUTES(
                        FactResellerSales,
                        'DimProductCategory'[EnglishProductCategoryName],
                        'FactResellerSales'[SalesAmount]
                    )
                ),
                'FactResellerSales'[SalesAmount]
            )
        ),
        "DimProductCategory_EnglishProductCategoryName_Order_null",
        CALCULATE(
            ROWNUMBERW(
                CALCULATETABLE(
                    DISTINCT(
                        SELECTATTRIBUTES(
                            DimProductCategory,
                            'DimProductCategory'[EnglishProductCategoryName]
                        )
                    )
                ),
                OrderBy(
                    'DimProductCategory'[EnglishProductCategoryName]
                )
            )
        )
    ),
    "DimProductCategory_EnglishProductCategoryName",
    'DimProductCategory'[EnglishProductCategoryName],
    "DimProductCategory_EnglishProductCategoryName_Order_null",
    [DimProductCategory_EnglishProductCategoryName_Order_null],
    "FactResellerSales_ MeasureCustomCategoryName _SUMX",
    [FactResellerSales_ MeasureCustomCategoryName _SUMX],
    "FactResellerSales_SalesAmount_SUMX",
    [FactResellerSales_SalesAmount_SUMX]
)