cancel
Showing results for
Did you mean: Anonymous
Not applicable

## Possible Analytics function: dynamic Total column in chart with USA States?

Hi Together!

Currently I'm trying to visualize a dynamic total column  in my chart with USA States (Red marked). The column should be seprated in bottleneck and regular jobs.

How I can realize it? Is there an analytics function like in other charts with a trendline or would it be possible with a measure?

At the moment I have just a solution with 2 charts, but I hope there's also one with just one chart. Maybe there's somebody with an idea to realize that?

Would be awesome!

BR,

Sven

1 ACCEPTED SOLUTION  Community Support

@Anonymous,

Drag [State] from the calculated table to Axis and measures below to Value.

```Measure =
VAR s =
SELECTEDVALUE ( 'Table'[State] )
RETURN
IF (
s = "Total",
SUM ( ValueTable[Regular jobs] ),
CALCULATE ( SUM ( ValueTable[Regular jobs] ), FactTable[State] = s )
)
```
```Measure 2 =
VAR s =
SELECTEDVALUE ( 'Table'[State] )
RETURN
IF (
s = "Total",
SUM ( ValueTable[Bottleneck jobs] ),
CALCULATE ( SUM ( ValueTable[Bottleneck jobs] ), FactTable[State] = s )
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
11 REPLIES 11 Regular Visitor

Hi,

@v-chuncz-msft Do you have any idea how to calculate the total for the sum of the week of each month?

Thanks!   Community Support

@Anonymous,

To use just one chart, you may add a calculated table and then create a measure.

```Table =
UNION ( VALUES ( Table1[Column1] ), ROW ( "Column1", "Total" ) )
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Anonymous
Not applicable

thank you for you idea, but how I can add now the sum of bottleneck and regular jobs for alle states and total?

Here's a part of my project and my connection.

I thought there would be a chart with the same "Total" function like with the table visualization.

Would be great if you could help me with this problem ! Facttable (ID = Key) Valuetable Calculated column Connection between Fact and Valuetable Diagramm and Table with Totals

BR,

Sven  Community Support

@Anonymous,

Drag [State] from the calculated table to Axis and measures below to Value.

```Measure =
VAR s =
SELECTEDVALUE ( 'Table'[State] )
RETURN
IF (
s = "Total",
SUM ( ValueTable[Regular jobs] ),
CALCULATE ( SUM ( ValueTable[Regular jobs] ), FactTable[State] = s )
)
```
```Measure 2 =
VAR s =
SELECTEDVALUE ( 'Table'[State] )
RETURN
IF (
s = "Total",
SUM ( ValueTable[Bottleneck jobs] ),
CALCULATE ( SUM ( ValueTable[Bottleneck jobs] ), FactTable[State] = s )
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  Helper I

Dear @v-chuncz-msft

Can you help me how can I implement the same with line values having % of total. Anonymous
Not applicable

thank you for your measure codes.

But unfortunately there's no other result: No total data Your measure code Connecting Tables

Do you have any ideas why there's no solution?

BR,

Sven  Community Support

@Anonymous,

Remove the relationship between FactTable and Table.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Anonymous
Not applicable

Dear @v-chuncz-msft,

Can you advise on adding an index column in calculated table 'Table', so I can sort the order of tenure categories on my chart properly? Otherwise the solution works great!

Thanks.  Anonymous
Not applicable

wooww! Thats kind of magic! It works! Thank you soo much!

BR,

Sven Anonymous
Not applicable

Hello everyone,

I need the very same total but do not have any fact + value table.
I only have one table with let's say 2 columns :

State and amount

I need a bar chart with

Axis : state

Value : amount

with on the left or the right a basic total (like in Excel)

I tried clustered column chart. It works fine except there is no total option.

Do I miss something?

I then tried to follow v-chuncz-msft solution but could not modify it to make it works with my simple data.

Phil  Helper I
`<img>https://fhi360web-my.sharepoint.com/:i:/g/personal/mmahachi_fhi360_org/EfYZ7h1Q_MtCh0Djb1F9UkEBtMtz_7VTwZ5-rIzj6Dj1ig?e=yxlCKP</img>` The solution didn't work for me, could it be because I only have the "total" name in a lookup table and not the fact?   