cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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.

 

2018-02-19 12_23_33-HR Monitor - Power BI Desktop.png

 

 

Maybe there's somebody with an idea to realize that?

Would be awesome!

 

BR,

 

Sven

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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

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

View solution in original post

9 REPLIES 9
Highlighted
Community Support
Community Support

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

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

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

Hi @v-chuncz-msft,

 

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)Facttable (ID = Key)

 

ValuetableValuetableCalculated columnCalculated columnConnection between Fact and ValuetableConnection between Fact and ValuetableDiagramm and Table with TotalsDiagramm and Table with Totals

BR,


Sven

Highlighted
Community Support
Community Support

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

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

View solution in original post

Highlighted
Anonymous
Not applicable

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

Hi @v-chuncz-msft;

 

thank you for your measure codes.

But unfortunately there's no other result:

 

No total dataNo total dataYour measure codeYour measure codeConnecting TablesConnecting Tables

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

 

BR,

 

Sven

 

Highlighted
Community Support
Community Support

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

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

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

Hi @v-chuncz-msft,

 

wooww! Thats kind of magic! It works!2018-02-28 09_56_25-Test - Power BI Desktop.png

Thank you soo much!

 

BR,

 

Sven

Highlighted
Helper I
Helper I

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

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

 

Highlighted
New Member

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

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

 

 

Highlighted
Helper I
Helper I

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

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.

 

Снимок экрана (9).png

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors