Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Adding subtotal columns based on a hierarchy Column Chart

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



Need Help
  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    2 years ago

    Hi Anonymous ,

     

    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

2 Replies

  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    Community Support
    2 years ago

    Hi Anonymous ,

     

    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