cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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.
Anonymous
Not applicable

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.
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

`<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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors