Forum Discussion

AZJohnPowerBI's avatar
AZJohnPowerBI
Helper I
2 years ago

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.

DAX Commands and Tips

3 Replies

  • AZJohnPowerBI's avatar
    AZJohnPowerBI
    Helper I
    2 years ago

    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?

  • AZJohnPowerBI's avatar
    AZJohnPowerBI
    Helper I
    2 years ago

    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.