Categorise measures to use as dimension

Question

I am trying to build a table where I am summing multiple measures and would like to categorise them into dimensions.

To simplify, I have a table in the warehouse with the below schema:

Profitability(month_end_date, product_type, existing_direct_variable_expenses, existing_direct_fixed_expenses, existing_indirect_expenses, new_direct_variable_expenses, new_direct_fixed_expenses)

In my measure I am summing existing_direct_variable_expenses, existing_direct_fixed_expenses, existing_indirect_expenses, new_direct_variable_expenses, new_direct_fixed_expenses for one specific product for a date filtered by the user.

I would like to display the below output

Expense | Amount

existing_direct_variable_expenses | 1,000

existing_direct_fixed_expenses | 200

existing_indirect_expenses | 1,500

new_direct_variable_expenses | 500

new_direct_fixed_expenses |50

I have tried to categorise it using the below formula, however it doesn't get past the first condition since each line has a value in each column in the dataset

if(NOT ISNULL(existing_direct_variable_expenses), 'Existing Direct Variable Expenses', 
    if(NOT ISNULL(existing_direct_fixed_expenses), 'Existing Direct Fixed Expenses',
        if(NOT ISNULL(existing_indirect_expenses), 'Existing Indirect Expenses',
            if(NOT ISNULL(new_direct_variable_expenses), 'New Direct Variable Expenses',
                if(NOT ISNULL(new_direct_fixed_expenses), 'New Direct Fixed Expenses')))))

I also had a play with AGGR function but had no luck.

I realise I could display as measures, however I will also be doing a column for Year To Date expenses and would like to maintain consistency along the other areas of my dashboard by having one column for a month and the breakdown as a dimension.

score 0 · Accepted Answer · answered Jun 15 '21 at 03:39

For references sake, I was able to complete the issue with a Value List. I added the below in my table as a dimension to display the categories:

=valuelist('Existing Direct Variable Expenses', 'Existing Direct Fixed Expenses', 'Existing Indirect Expenses', 'New Direct Variable Expenses', 'New Direct Fixed Expenses')

I then added this into my measure to correctly sum the values against each of the dimensions.

if(valuelist('Existing Direct Variable Expenses', 'Existing Direct Fixed Expenses', 'Existing Indirect Expenses', 'New Direct Variable Expenses', 'New Direct Fixed Expenses')='Existing Direct Variable Expenses',
    sum({<product_type={'Sales'}>} existing_direct_variable_expenses),
if(valuelist('Existing Direct Variable Expenses', 'Existing Direct Fixed Expenses', 'Existing Indirect Expenses', 'New Direct Variable Expenses', 'New Direct Fixed Expenses')='Existing Direct Fixed Expenses',
    sum({<product_type={'Sales'}>} existing_direct_fixed_expenses),
if(valuelist('Existing Direct Variable Expenses', 'Existing Direct Fixed Expenses', 'Existing Indirect Expenses', 'New Direct Variable Expenses', 'New Direct Fixed Expenses')='Existing Indirect Expenses',
    sum({<product_type={'Sales'}>} existing_indirect_expenses),
if(valuelist('Existing Direct Variable Expenses', 'Existing Direct Fixed Expenses', 'Existing Indirect Expenses', 'New Direct Variable Expenses', 'New Direct Fixed Expenses')='New Direct Variable Expenses',
    sum({<product_type={'Sales'}>} new_direct_variable_expenses),
if(valuelist('Existing Direct Variable Expenses', 'Existing Direct Fixed Expenses', 'Existing Indirect Expenses', 'New Direct Variable Expenses', 'New Direct Fixed Expenses')='New Direct Fixed Expenses',
    sum({<product_htype={'Sales'}>} new_direct_fixed_expenses))))))

Categorise measures to use as dimension

1 Answers1