How can I show a Total for CY & YA in a side‑by‑side bar chart (clustered column) in Power BI?

[Ignite190]

Hi all,
I have a clustered bar/column chart showing CY and YA side by side for categories (A, B, C). I want to add a “Total” category at the end where CY Total and YA Total also appear side by side (not stacked).

Since Power BI doesn’t support totals natively for clustered charts, what’s the recommended DAX / modeling approach to achieve this cleanly?

Any guidance or examples appreciated. Thanks! 😊

[v-kpoloju-msft]

Hi @Ignite190, 

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to @SharmilaBrisca, @cengizhanarslan,  for those inputs on this thread.

Has your issue been resolved? If the response provided by the community member @cengizhanarslan  addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[cengizhanarslan · Accepted solution]

Step 1) Create a disconnected category table that includes Total

DimCategory =
UNION (
    VALUES ( FactTable[Category] ),
    ROW ( "Category", "Total" )
)

Step 2) Create CY and YA measures that handle the Total row

CY Sales =
VAR _SelectedCategory =
    SELECTEDVALUE ( DimCategory[Category] )
RETURN
    IF (
        _SelectedCategory = "Total",
        CALCULATE ( SUM ( FactTable[Sales] ), REMOVEFILTERS ( DimCategory ) ),
        CALCULATE ( SUM ( FactTable[Sales] ), FactTable[Category] = _SelectedCategory )
    )

YA Sales =
VAR _SelectedCategory =
    SELECTEDVALUE ( DimCategory[Category] )
RETURN
    IF (
        _SelectedCategory = "Total",
        CALCULATE ( [YA Base Measure], REMOVEFILTERS ( DimCategory ) ),
        CALCULATE ( [YA Base Measure], FactTable[Category] = _SelectedCategory )
    )

Replace YA Base Measure with your existing prior year measure.

Step 3) Configure the clustered column chart

X axis: DimCategory[Category]
Y axis: CY Sales and YA Sales as two separate measures

Step 4) Control sort order
Add a sort column to DimCategory so Total always appears last:

DimCategory =
UNION (
    ADDCOLUMNS ( VALUES ( FactTable[Category] ), "SortOrder", 1 ),
    ROW ( "Category", "Total", "SortOrder", 2 )
)

Then sort DimCategory[Category] by SortOrder.

[SharmilaBrisca]

Hi @Ignite190,

Build a calculated table that unions your dynamic categories with a hard-coded "Total" row. This becomes the X-axis of your chart.

CategoryAxis =
UNION(
DISTINCT( FactTable[Category] ),         -- your dynamic categories
ROW( "Category", "Total" )                  -- appended Total row
)

Set a Sort Order column in this table so "Total" always appears last regardless of alphabetical sort. Create a index value for each categories and Total count of categories. If Category is Total then sorting number is Total count+1 or else index number....

Now sort categories column based on the sorting column.

Then create a measure like below
CY Value =
SWITCH( TRUE(),
SELECTEDVALUE( CategoryAxis[Category] ) = "Total",
CALCULATE( [CY Value], ALL( CategoryAxis ) ),
[CY Value]
)

Hope I answered Your Question

Thanks and Regards

GainInsights Solutions
Trusted Microsoft Partner - https://gain-insights.com/partnerships/microsoft/
www.gain-insights.com 

[Ignite190]

This will double the data size .