Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I
Helper I

Total Bar over Months

@Greg_Deckler @amitchandak 

I am trying to get a graph divided by months to show a total bar.

When I use a similar dax formula by categories to show a total bar, it works fine.

When I try to create a table in Excel (upstream) or within Power BI, it either shows the months out of order, or shows the months in order but no total bar.


ScheduledAppts_Total =
SELECTEDVALUE( TotalBar_MY[Month Year]) = "Total"
, CALCULATE( SUM( 'Fact Epic'[Scheduled Appointments] ), ALL( TotalBar_MY[Month Year] ) )
, SUM('Fact Epic'[Scheduled Appointments])

I know something similar works when I use it over categories:

Below I have shown the table view and the report view.

Here is the DAX for the other bar graphs, which show the total over categories successfully.
CSQ_Total_Bar = UNION(DISTINCT('Fact Cisco'[CSQ Name]),{"Total"})
For Abandonment Rate
Total_AbandonmentRate =
SELECTEDVALUE(CSQ_Total_Bar[CSQ Name]) = "Total"
, CALCULATE(Average('Fact Cisco'[Abandonment Rate]), ALL(CSQ_Total_Bar[CSQ Name]))
, Average('Fact Cisco'[Abandonment Rate]))

For Service Level
Total_CSQ_SVL =
SELECTEDVALUE(CSQ_Total_Bar[CSQ Name]) = "Total"
, CALCULATE(Average('Fact Cisco'[Service Level]), ALL(CSQ_Total_Bar[CSQ Name]))
, Average('Fact Cisco'[Service Level]))


I have tried several different ways that I've seen on youtube, but nothing seems to work. Here's what the table data looks like, there's a total row and index value at the bottom. The index is to sort the months.Here's what the table data looks like, there's a total row and index value at the bottom. The index is to sort the months.Total_Bar_ReportView.png

Helper I
Helper I

There's a new problem on this, after the update.

The same code format works on 4 of the graphs, but not another.


Total_TypeBar =
    SELECTEDVALUE( TotalBar_Type[Type] ) = "Total"
    , CALCULATE( SUM('Fact Epic'[Scheduled Appointments]), ALL ( TotalBar_Type[Type] ) )
    , SUM( 'Fact Epic'[Scheduled Appointments] ))
Totals_HandleRatio =
    SELECTEDVALUE(Total_Bar_CSQ[CSQ Name]) = "Total"
    , CALCULATE(AVERAGE('Fact Cisco'[Handle Ratio]), ALL(Total_Bar_CSQ[CSQ Name]))
    , AVERAGE('Fact Cisco'[Handle Ratio]))
For example, used to work fine. Now, only the first code mentioned works but not the second code.
Any help would be greatly appreciated.
Helper I
Helper I

Hey tamerj1,

good question.

When I have the total bar show up, and months are out of order (because they are text formatted and not date, "Jan 2024"), I am unable to create an index column to sort the months.

I have tried several different ways of making an index column (one in Excel, one in Power Query, on using Index function, RankX...etc).


I think the measures require using a calculated table (in this case
table = UNION(DISTINCT('DIM Date'[Month Year]),{"Total"}) instead of using a table made from Power Query or Excel.
If I use RankX or Index functions, it sorts is alphabetically, so I use related, but I get a cyclical reference error.

In this way, I am unable to sort the months.


Is there another way?

Super User
Super User

Hi @AZJohnPowerBI 

Have you set up the sort by column properly?


Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.


Fabric certifications survey

Certification feedback opportunity for the community.