Forum Discussion

Anonymous

Not applicable

8 years ago

Solved

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

Need Help

v-chuncz-msft

8 years ago

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

v-chuncz-msft

Community Support

8 years ago

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
Not applicable
8 years ago
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
  Community Support
  8 years ago
  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
    Not applicable
    8 years ago
    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