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)

ValuetableCalculated columnConnection between Fact and ValuetableDiagramm 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.
Frequent Visitor

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 dataYour measure codeConnecting 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.
Helper I

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.

Thanks for your help

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?

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors