topic Re: Total Bar over Months in DAX Commands and Tips

Total Bar over Months

AZJohnPowerBI — Thu, 02 May 2024 15:41:36 GMT

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.

Re: Total Bar over Months

tamerj1 — Mon, 22 Apr 2024 21:01:45 GMT

Hi AZJohnPowerBI

Have you set up the sort by column properly?

Re: Total Bar over Months

AZJohnPowerBI — Tue, 23 Apr 2024 14:12:28 GMT

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?

Re: Total Bar over Months

AZJohnPowerBI — Thu, 02 May 2024 15:35:34 GMT

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.