Forum Discussion

Anonymous's avatar
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 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

Need Help
  • v-chuncz-msft's avatar
    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 )
    )

11 Replies

  • v-chuncz-msft's avatar
    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's avatar
      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's avatar
        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 )
    )
  • jorgeluiz's avatar
    jorgeluiz
    Regular Visitor
    5 years ago

    Hi,

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

     

    Thanks!