Showing a aggregated column in stacked column chart

[Aswin]

Is there any way to show a column with the aggregated values of other columns

What I need is in the below visual I need a column that shows the total Billable, Free, Investment across BU's as the first stacked column of the chart.

Thanks in advance

[Eric_Zhang]

---

@Aswin wrote:

Is there any way to show a column with the aggregated values of other columns

What I need is in the below visual I need a column that shows the total Billable, Free, Investment across BU's as the first stacked column of the chart.

Thanks in advance

---

@Aswin

To get the expected output, you can create an extra table and create a one to many relationship between the created table and your datatable.

BU_Table = UNION(VALUES(yourDataTable[BU]),DATATABLE("BU",STRING,{{"_Total"}}))

Then create a measure as below

values =
IF (
    MAX ( 'BU_Table'[BU] ) = "_Total",
    CALCULATE ( SUM ( yourDataTable[Amount] ), ALL ( yourDataTable ) ),
    SUM ( yourDataTable[Amount] )
)

See more details in the attached pbix file.

[Aswin]

@Eric_Zhang  As per your solution, the stacks in the total column does not add up as expected.
I ended up creating a calculated table as shown below

Resource_Util = 
  UNION(SUMMARIZECOLUMNS(Employee[Department],Employee[Status],"Count",COUNT(Employee[Status])),
        SELECTCOLUMNS(SUMMARIZECOLUMNS(Employee[Status],"count",COUNT(Employee[Status])),"Department","Total","Status",Employee[Status],"Count",[count]))
        

with three added rows for each status(Billable,Investment,Free) and their total across Departments.

Department | Status | Count

 AN-BU| Billable | 6

 AN-BU| Investment |1 

    : : : : : :

    : : : : : :
 Total | Investment| 2

 Total | Billable |12

 Total |Free | 1

But the aggregated column bar has no highlighting feature by which we can see how much of the total is taking up when other stacked bars are clicked on.

[Aswin]

@Eric_Zhang Is MAX('BU_Table'[BU]) possible given that 'BU_table'[BU] is a string/text ?

[Aswin]

Thanks a lot, @Eric_Zhang.I will give it a try and update the thread