What is the best way to calculate?

So I'm using a grouped bar graph and am looking to try and calculate the difference between two of my columns that were created.

My company calculates the average spread on loans broken down by lenders we use. I was able to separate those average spreads by product by adding the products to the 'series' drop-in at the top.

The next thing I'm trying to do is calculate the average spread on our 1st home product vs our home again product, but it still has to be broken down by lender.

(I did not include the lender names but each blue/green bar pairing represent an individual lender)

In essence, I am trying to create a formula where we subtract the blue columns from the green columns. TIA

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

Best Answer

  • RobSomers
    RobSomers 🟢
    Answer ✓

    @damen You'll need to remove 'instrument' from Series, and create the separate beast modes for each instrument you want to measure. Ex:

    AVG(case when 'instrument'= 'first home exempt' then 'spread' end)

    and a separate one:

    AVG(case when 'instrument' = 'home again' then 'spread' end)

    Then you'll take one of these and put in Value, and then put the other one in Series as well as the difference beast mode in Series You'll end up with something that looks like this:

    It's not very intuitive and it looks weird, but it works.

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

Answers

  • @damen You should be able to do a beast mode like the following (using 'Product' for your 1st Home or Home Again column):

    AVG(case when 'Product' = '1st Home' then 'spread' end) - AVG(case when 'Product' = 'Home Again' then 'spread' end)

    If you want to keep your chart as is and have the difference as another bar, you'll need to do separate beast mode calculations for the spread of 1st Home and the spread of Home Again, then add those and the difference beast mode to the chart (same goes for doing a bar and line chart).

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • damen
    damen 🟠
    edited September 27

    @RobSomers For whatever reason, I'm not getting any calculations

    AVG(case 

        when (CASE 

    WHEN (`instrument` = 'first home') THEN '1st Home' 

     WHEN (`instrument` = 'fannie mae risk share') THEN 'NoMi'

     WHEN (`instrument` = 'fha streamline') THEN 'FSR' 

     WHEN (`instrument` = 'first home tax exempt') THEN '1st Home' 

     WHEN (`instrument` = 'freddie mac advantage ') THEN 'HFA Advantage' 

     WHEN (`instrument` = 'freddie mac risk share') THEN 'NoMi' 

     WHEN (`instrument` = 'freddie mac advantage') THEN 'HFA Advantage' 

     WHEN (`instrument` = 'fsr') THEN 'FSR' 

     WHEN (`instrument` = 'home again') THEN 'Home Again' 

     WHEN (`instrument` = 'NoMI') THEN 'NoMi'

     WHEN (`instrument` = 'score') THEN 'Score' 

     ELSE `instrument` 

     

    END) = '1st Home' then `spread` end) 

     

     - AVG(case 

        when 

        

        (CASE 

     

     WHEN (`instrument` = 'first home') THEN '1st Home' 

     WHEN (`instrument` = 'fannie mae risk share') THEN 'NoMi'

     WHEN (`instrument` = 'fha streamline') THEN 'FSR' 

     WHEN (`instrument` = 'first home tax exempt') THEN '1st Home' 

     WHEN (`instrument` = 'freddie mac advantage ') THEN 'HFA Advantage' 

     WHEN (`instrument` = 'freddie mac risk share') THEN 'NoMi' 

     WHEN (`instrument` = 'freddie mac advantage') THEN 'HFA Advantage' 

     WHEN (`instrument` = 'fsr') THEN 'FSR' 

     WHEN (`instrument` = 'home again') THEN 'Home Again' 

     WHEN (`instrument` = 'NoMI') THEN 'NoMi'

     WHEN (`instrument` = 'score') THEN 'Score' 

     ELSE `instrument` 

     END) = 'Home Again' then `spread` end)


    That is the 'asdf' beast mode


    What I failed to mention was the fact that we had to create the 'product' bucket to begin with. That beast mode includes the syntax for that to happen.


    The beast mode comes back valid but again, not sure why I wouldn't be getting any values.

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

  • @RobSomers

    I even tried to do it without the instrument buckets beast modes taking place

    still no values.

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

  • That works. Thank you

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

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🔴

    @damen

    the learning lesson is you shouldn't have nested CASE statements

    AVG(
    case 
    when (CASE
    WHEN (`instrument` = 'first home') THEN '1st Home' 
    WHEN (`instrument` = 'fannie mae risk share') THEN 'NoMi'
    WHEN (`instrument` = 'fha streamline') THEN 'FSR' 
    WHEN (`instrument` = 'first home tax exempt') THEN '1st Home' 
    WHEN (`instrument` = 'freddie mac advantage ') THEN 'HFA Advantage' 
    WHEN (`instrument` = 'freddie mac risk share') THEN 'NoMi' 
    WHEN (`instrument` = 'freddie mac advantage') THEN 'HFA Advantage' 
    WHEN (`instrument` = 'fsr') THEN 'FSR' 
    WHEN (`instrument` = 'home again') THEN 'Home Again' 
    WHEN (`instrument` = 'NoMI') THEN 'NoMi'
    WHEN (`instrument` = 'score') THEN 'Score' 
    ELSE `instrument` 
    END) = '1st Home' then `spread` end) 
    

    because if you boil it down.

    your inner CASE statement will return a number.

    so then in your outer CASE statement you have

    CASE when <number> = '1st Home' then spread end.

    well... it'll never = '1st Home'. so you always return NULL because you don't have an ELSE clause in place.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"