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

[Anonymous]

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

[jorgeluiz]

Hi,

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

Thanks!

 

[v-chuncz-msft]

@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" ) )

[Anonymous]

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)

ValuetableCalculated columnConnection between Fact and ValuetableDiagramm and Table with Totals

BR,

Sven

[v-chuncz-msft · 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 )
    )

[Anonymous]

Dear @v-chuncz-msft 

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

[Anonymous]

Hi @v-chuncz-msft;

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

[v-chuncz-msft]

@Anonymous,

Remove the relationship between FactTable and Table.

[Anonymous]

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]

Hi @v-chuncz-msft,

wooww! Thats kind of magic! It works!

Thank you soo much!

BR,

Sven

[Anonymous]

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

[Mmahachi]

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