Adding subtotal columns based on a hierarchy Column Chart

[jmdatasci]

Hi there, I have my data set up in a way that I have a time hierarchy - Year>Half>Quarter like this

I have the data for each quarter which aggregates up properly etc. 

What I would like to do is change my chart from this:

to something like this:

Where the dark blue subtotal/total columns are an aggregate of the lower level data either a sum or average. Is there any way to do this without going too crazy? 

Thanks

[v-weiyan1-msft · Accepted solution]

Hi @jmdatasci ,

From your description, please try the following steps:

1.Please try code as below to Create a X-axis table.

X-axis = 
var _Year = VALUES('Table'[Year])
var _Qrt =
DATATABLE(
 
    " X axis",STRING,
    "Order",INTEGER,
    {
        {"Q1",1},
        {"Q2",2},
        {"H1",3},
        {"Q3",4},
        {"Q4",5},
        {"H2",6},
        {"FY",7}
    }
)
return
GENERATE (
  _Qrt, _Year
)​

2.Use the following code to create two measures, Value_Measure and Color.

Value_Measure = 
var _QTR = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Year] = MAX('X-axis'[Year]) && 'Table'[Quarter] = MAX('X-axis'[ X axis])))
var _H1 = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Year] = MAX('X-axis'[Year]) && 'Table'[Quarter] IN {"Q1","Q2"}))
var _H2 = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Year] = MAX('X-axis'[Year]) && 'Table'[Quarter] IN {"Q3","Q4"}))
var FY = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Year] = MAX('X-axis'[Year])))
return
IF(_QTR = BLANK(),
    SWITCH(MAX('X-axis'[ X axis]),
           "H1",_H1,
           "H2",_H2,
           "FY",FY
           ),
    _QTR
    )
​

Color = IF(CONTAINSSTRING(MAX('X-axis'[ X axis]),"Q"),"#118DFF","#12239E")​

3.Select your visual object and do the following in the Visualizations pane.

Result is as below.

My Sample:

Best Regards,

Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jmdatasci]

Thank you so much Yulia! It works, just wanted to add for anyone else looking at this that you can sort the X axis column on the order column like this:
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column?tabs=powerbi-deskto...