Skip to main content
cancel
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.

Reply
AZJohnPowerBI
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 =
SWITCH(
TRUE(),
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 =
SWITCH(TRUE(),
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 =
SWITCH(TRUE(),
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

3 REPLIES 3
AZJohnPowerBI
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 =
SWITCH( TRUE (),
    SELECTEDVALUE( TotalBar_Type[Type] ) = "Total"
    , CALCULATE( SUM('Fact Epic'[Scheduled Appointments]), ALL ( TotalBar_Type[Type] ) )
    , SUM( 'Fact Epic'[Scheduled Appointments] ))
 
Totals_HandleRatio =
SWITCH(TRUE(),
    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.
 
AZJohnPowerBI
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?

tamerj1
Super User
Super User

Hi @AZJohnPowerBI 

Have you set up the sort by column properly?

IMG_3329.jpeg

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.