Average for Nested Bar instead of Total, or another option?

John-Peddle
John-Peddle Contributor
in Charting

Good morning. We'd like to show the average of the two bars in this card, and ideally it would be using a Nested Bar chart but open to other suggestions (currently using a Grouped Bar chart).

CURRENT:
Screen Shot 2019-05-10 at 10.00.12 AM.png

 

 

 

 

 

 

 

NESTED BAR:
Screen Shot 2019-05-10 at 10.00.37 AM.png

 

 

 

 

 

 

 

The Value is a beast mode, which takes into consideration the actual sale amount less the valuation, divided by the valuation: (CASE WHEN `Valuation` = 0 THEN 0 ELSE (SUM(`SaleAmount` - `Valuation`)) / SUM(`Valuation`) END)

The Series consists ot the two (2) stores, the bars in the cart would be Store 1 and Store 2.

 

Any thoughts on how we can achieve this, i.e. an average bar, or line, to show the average of both Valuation to Sale Amount Variances by Store? Thanks

Comments

  • JasonAltenburg
    JasonAltenburg Coach

    Have you tried utilizing the Line+nested bar chart type?

     

    In this example I've created a simple beast mode for an average of my values for A1, A2, and A3 divided by 3 and plugged that into the Y Axis dimension.

     

    (`A1`+`A2`+`A3`)/3

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

  • John-Peddle
    John-Peddle Contributor

    Thanks, @JasonAltenburg, believe that got us in the right direction! 

    Wat we've done is separate the "stores" out into their own columns (Store 1 & Store 2), and you can see from image 1 that only the row of data that applies to that store appears in the store specific column that was created with a beast mode.Image_1.png

     

     

     

     

     

     

     

     

     

    However, we're still not able to properly calculate the individual stores so their bar shows the correct variance seen in image 2 (top portion). The total is correct on the existing card (top) and the new card (bottom), but you'll see the difference in the store variances and the new card (bottom) is incorrect.

     

    Image_2.png

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Believe it may have something to do with the null values in the newly created columns, and have tried several beast modes to account for them but haven't been able to get it so they reconcile with the individual store variances from the existing card (top).

     

    This beast mode, which separates the stores into new columns, is where we also have to calcualte the correct percentage of 2.95% for each, and this is what we've come up with so far that brings us closest to what we need but still not accurate. 

     

    (CASE
    WHEN `ParentStore` = 'Store 1' THEN (SUM(IFNULL(`Valuation To Sale Amount Variance`,0)) / COUNT(IFNULL(`Valuation To Sale Amount Variance`,0)))
    END)

     

    Any additional assistance would be greatly appreciated! 

  • John-Peddle
    John-Peddle Contributor

    Any ideas on why we're unable to get the individual stores to reconcile? Is there anything additional that we could provide to help understand the ask better? Thank you in advance for any assistance on this!

  • ST_-Superman-_
    ST_-Superman-_ Coach

    Could you try this as your beastmode?

     

    sum(CASE
    WHEN `ParentStore` = 'Store 1' THEN IFNULL(`Valuation To Sale Amount Variance`,0) end)
     / 
    sum(case 
    when `ParentStore`='Store 1' and ABS(ifnull(`Valuation To Sale Amount Variance`,0))>0 then 1 else 0 end)

    I'm pretty sure that this expression will count the 0's as a value as well:

    COUNT(IFNULL(`Valuation To Sale Amount Variance`,0)))

     

    which is why the average was getting thrown off.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • ST_-Superman-_
    ST_-Superman-_ Coach

    I did a little bit of testing to confirm my previous post:1.png

     

     

    I think it really depends on your expected result.  I used three different beastmodes to test the outcomes of each.  

    1. count ifnull

    COUNT(IFNULL(`count`,0)))

    This was testing the denominator in your original field.  This does, indeed, count a value for every row even if there was a null in the dataset.

     

    2. count()

    count(`count`)

    This will include a count for any row with any value (exlcudes null values)

     

    3. sum()

    sum(case when ABS(IFNULL(`count`,0))>0 then 1 else 0 end)

    This will tell you the number of rows with a value (excludes 0's and nulls)


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • John-Peddle
    John-Peddle Contributor

    @ST_-Superman-_would it be ok to DM you, maybe provide some of the raw data we're working with via Excel?

     

    Still unable to get this to work as expected, not sure if it has to do with WHAT we're doing or maybe related to the information provided in this thread not being thorough enough to get the correct feedback on a solution. Thanks!

This discussion has been closed.

Categories