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

@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

11 REPLIES 11
jorgeluiz
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!

 
 

Sem título.png

v-chuncz-msft
Community Support
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

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

@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

Dear @v-chuncz-msft 

 

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

Anonymous
Not applicable

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

 

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

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

Anonymous
Not applicable

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

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

 

 

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

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors