Sub-Category of a Category?

damen
damen Contributor
edited August 20 in Magic ETL

Hi All,

We are working on a report that has a few different program_namesthat are summed up in a pie chart using orig_loan_amount.

I have been tasked with trying to sub categorize our bonds using a taxable/tax-exempt designation

The problem I am trying to overcome is that our bonds (identified by pool_name) are actually made up of both. There is no designation on a single line.

So for example:

Loan number 157214's total loan amount was 373k+ - it was distributed into our 2024EF Bond. 40% of it is taxable and 60% is TE.

image.png


This would be pretty straight forward if I was only trying to show a breakdown of the bonds and what the taxable/TE amount was BUT those pool_names that have BOND in them get put into a program name that is bonds - and there are 3 other programs listed in our pie chart.

image.png

Essentially, I need this pie chart to show 60% tax-exempt and 40% taxable - just for the Bonds. AND I also have to show the GNMA MBS, the 'Blanks', and the FNMA/FREDDIE on the same visualization.

Is this possible?

If this helps, feel free to agree, accept or awesome it!

Tagged:

Best Answers

  • nmizzell
    nmizzell Contributor
    Answer ✓

    Hi Damen,

    To accomplish this task, you only need two columns:

    1. Bond Category
    2. Amount

    Using Magic ETL, perform the following steps:

    1. keep only the amount, name, taxable, and tax-exempt columns
    2. create a helper column that reads the name of each bond, and classifies it as 'bonds', 'MBS', 'Freddie', or 'other'. We will break out the 'bonds' category into taxable and non-taxable subcategories in the next step.
    3. unpivot the taxable and tax-exempt columns into rows. This will create two rows for all bonds with values in both taxable and non-taxable columns. One row will be the taxable portion, the other row will be the non-taxable portion. The values should be entered into a new column 'Amounts', and the two categories should be 'bonds- taxable' and 'bonds - non-taxable'.

    Now that you have your dataset prepared, it should have only two columns: 'Bond Category' and 'Amount'. These are the columns that you can chart in your pie chart.

    Pro tip: make sure that a pie chart is the appropriate visual you need to use for this data. Pie charts are often not recommended because it is difficult to tell the differences between the sizes of the slices. However, if your intention is to convey a general relationship, the pie chart will do just fine.

    See the SWD blog on pie charts:

    What is a pie chart and when to use it — storytelling with data

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    Unpivoting the taxable/untaxable would be helpful. The sunburst chart is helpful for breaking out a pie-chart into subcategories, with helpful default tooltips aligned to what you want:

    image.png

    A treemap is also nice for showing subcategories. One disadvantage of the sunuburst is the area of the sub-categories is too visually big and the rotated labels are hard to read, which the treemap fixes. In this case, it would take more work to surface the "60% of Bonds" that you want, though.

    image.png

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Answers

  • nmizzell
    nmizzell Contributor
    Answer ✓

    Hi Damen,

    To accomplish this task, you only need two columns:

    1. Bond Category
    2. Amount

    Using Magic ETL, perform the following steps:

    1. keep only the amount, name, taxable, and tax-exempt columns
    2. create a helper column that reads the name of each bond, and classifies it as 'bonds', 'MBS', 'Freddie', or 'other'. We will break out the 'bonds' category into taxable and non-taxable subcategories in the next step.
    3. unpivot the taxable and tax-exempt columns into rows. This will create two rows for all bonds with values in both taxable and non-taxable columns. One row will be the taxable portion, the other row will be the non-taxable portion. The values should be entered into a new column 'Amounts', and the two categories should be 'bonds- taxable' and 'bonds - non-taxable'.

    Now that you have your dataset prepared, it should have only two columns: 'Bond Category' and 'Amount'. These are the columns that you can chart in your pie chart.

    Pro tip: make sure that a pie chart is the appropriate visual you need to use for this data. Pie charts are often not recommended because it is difficult to tell the differences between the sizes of the slices. However, if your intention is to convey a general relationship, the pie chart will do just fine.

    See the SWD blog on pie charts:

    What is a pie chart and when to use it — storytelling with data

  • damen
    damen Contributor

    @nmizzell

    Thanks for the info - After posting this question and working through the process, I got to the point where I thought of just about the same solution. Thank you for confirming to me that this was in fact the way!

    If this helps, feel free to agree, accept or awesome it!

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    Unpivoting the taxable/untaxable would be helpful. The sunburst chart is helpful for breaking out a pie-chart into subcategories, with helpful default tooltips aligned to what you want:

    image.png

    A treemap is also nice for showing subcategories. One disadvantage of the sunuburst is the area of the sub-categories is too visually big and the rotated labels are hard to read, which the treemap fixes. In this case, it would take more work to surface the "60% of Bonds" that you want, though.

    image.png

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Categories