additional category for the calculation

[RMV]

Hi,

I'm trying to create a matrix/table

For example:

Transaction table

Date            Amount     Category            Additional Category

1-Jan-17       1000            A

2-Jan-17         500            B                          Add-1

3-Jan-17        1500           C

4-Jan-17         700            A                          Add-1

The table/matrix result

Category           Amount

A                        1700 (1000 + 700)

B                          500

C + Add-1          2700 (1500 + 500 + 700)

Any idea how to create this in a most efficient way?

Thanks

[bullius]

Hi @RMV,

I am not sure if this is the most efficient way. Nor does it produce exactly what you asked for, but see what you think.

This Matix/Table is the end result:

Steps:

First add a custom column to contain another category (Query Editor):

    if [Category] = "C" or [Additional Category] = "Add-1" then "C+Add-1" else "Other"

Then create 3 measures:

    1.    CategoryA = CALCULATE ( SUM ( Table1[Amount] ), Table1[Category] = "A" )

    2.    CategoryB = CALCULATE ( SUM ( Table1[Amount] ), Table1[Category] = "B" )

    3.    C+Add-1 = CALCULATE ( SUM ( Table1[Amount] ), Table1[NewCategory] = "C+Add-1" )

Lastly, add these measures to the Values bucket of a Matix/Table.

Not perfect, but close to what you're asking for.

[RMV]

Hi @bullius,

Thanks for your advise. It's a great idea. However, my example was more simple than the actual case. I have other bigger categories , and I need to create a stepped layout format.

Your idea is quite efficient. But, is there a way to combine the calculated columns to drill up to bigger categories?

What I can think of now is if I'm using KPI card for the calculated columns, I may have combine it with table/matrix. It requires more space though.

[v-yulgu-msft · Accepted solution]

Hi @RMV,

You can create a new table based on below formula, and add a calculated column to this new table.

New Table1 =
UNION (
    SELECTCOLUMNS (
        'Transaction table',
        "Category", 'Transaction table'[Category],
        "Amount", 'Transaction table'[Amount]
    ),
    SELECTCOLUMNS (
        FILTER (
            'Transaction table',
            'Transaction table'[Additional Category] <> BLANK ()
        ),
        "Category", "Add-1",
        "Amount", 'Transaction table'[Amount]
    )
)

new Category =
IF (
    'New Table1'[Category] <> "A"
        && 'New Table1'[Category] <> "B",
    "C+Add-1",
    'New Table1'[Category]
)      

       

Best regards,
Yuliana Gu

[RMV]

Hi @v-yulgu-msft,

I tried to modify the calculation a bit.

New Table1 =
UNION (
    SELECTCOLUMNS (
        'Transaction table',
        "Category", 'Transaction table'[Category],
        "Amount", 'Transaction table'[Amount]
    ),
    SELECTCOLUMNS (
        FILTER (
            'Transaction table',
            'Transaction table'[Additional Category] = "Add-1"
        ),
        "Category", "C",
        "Amount", 'Transaction table'[Amount]
    )
)

But, when I create the table, the total amount of "Add-1" is not summed up to Category C.

Is there anything wrong with my formula above?

Thanks

[v-yulgu-msft]

Hi @RMV,

Based on my test, the modified formula you posted above worked fine. The total amount of "Add-1" can be summed up to Category C. In your scenario, please check whether there existing any non-printable character like space in field values. What is the result you have got currently? Please show an image.

Regards,
Yuliana Gu

[RMV]

Hi @v-yulgu-msft, I got what caused the error now. It's because the arrangement of columns are not matched among the tables I tried to combine.

Thanks for your assistance to check.

[RMV]

Hi @v-yulgu-msft, thanks for the help!

It works