Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

How to display the table Total in grouped column chart

Melissa de Korte

Melissa de KorteSep 14, 2021 10:27 AM

  • 1.  How to display the table Total in grouped column chart

    Posted Sep 13, 2021 05:16 PM

    I need to show the total at the bottom of a data table in a grouped column chart in Power BI, that is, this total which is found here at the bottom of the table:


    I want to show it as a value in a grouped column chart next to the value of 387 as follows:


    It is important to make it clear that the columns from which the value is obtained 4:7 4:42 5:38 are columns made from a measure, that is, a DAX formula was used for its elaboration, for this reason I am not allowed to drag the column to the Values ​​section in the clustered column chart, every time I try to place it in Values ​​it automatically moves to Tooltip.

    The column PromedioHoraPlanta is the one that corresponds to the total displayed value of 4: 7 in the table.

    I would like someone to give me guidance on how to place the total in the grouped column graph

     



    ------------------------------
    John Doe
    ------------------------------


  • 2.  RE: How to display the table Total in grouped column chart

    Top Contributor
    Posted Sep 14, 2021 08:03 AM

    Not possible until you convert your duration into a numeric value.

    Times and durations data are like dates and text, you can only put them in x axis (not in Values). They are considered as categorical data in a visual like line chart, column chart or bar chart.

    Also, if you look closely to your screenshot, the value 4:7 is not really the total for that column. This is because the days part is hidden from the format.

    ------------------------------
    If any discussion or answer help, please mark it as "Recommend"
    ------------------------------

    Best regards
    Mehdi HAMMADI

    MCT | MCSA BI Reporting | MCSE Data Management and Analytics | Data Analyst Associate
    2019 Ruby Award Dynamic Communities
    ------------------------------



  • 3.  RE: How to display the table Total in grouped column chart

    Posted Sep 14, 2021 09:21 AM

    The value 4: 7 and the others I obtain from the following measure:

    = FORMAT(AVERAGE(Consulta1[t_TOTAL_SALIDA_seg])/3600 , "##") & ":" & FORMAT(MOD(AVERAGE(Consulta1[t_TOTAL_SALIDA_seg]),60), "##" )

    Is there any way to convert it to numeric data? 


    ------------------------------
    John Doe
    ------------------------------



  • 4.  RE: How to display the table Total in grouped column chart

    Top Contributor
    Posted Sep 14, 2021 10:14 AM
    why not just using
    AVERAGE( Consulta1[t_TOTAL_SALIDA_seg] )​

    This calculation return a numeric value and it is what you are using to get the format.

    Question: what is the unit used for the [t_TOTAL_SALIDA_seg] column, hours minutes or seconds???



    ------------------------------
    If any discussion or answer help, please mark it as "Recommend"
    ------------------------------

    Best regards
    Mehdi HAMMADI

    MCT | MCSA BI Reporting | MCSE Data Management and Analytics | Data Analyst Associate
    2019 Ruby Award Dynamic Communities
    ------------------------------



  • 5.  RE: How to display the table Total in grouped column chart

    Posted Sep 14, 2021 10:27 AM
    Edited by Melissa de Korte Sep 14, 2021 10:28 AM


  • 6.  RE: How to display the table Total in grouped column chart

    Posted Sep 14, 2021 12:07 PM
    I forgot to comment to you because I use the DAX formula that I added in the previous comment, this formula is used to convert a range of seconds into the average but converted into hours/seconds format

    For example, if you have 3 records : enter image description here

    You will get 13:32 if you respect the format ## for the Number and the mod60.

    Regarding your question, the unit used for the column [t_TOTAL_SALIDA_seg] is whole number.



    ------------------------------
    John Doe
    ------------------------------



  • 7.  RE: How to display the table Total in grouped column chart

    Top Contributor
    Posted Sep 14, 2021 03:37 PM
      |   view attached
    As I already mentioned, to show duration data in a column chart visual you need to have the data as numeric format.

    The column you are using provides duration in seconds. You can just use the average (and show seconds) or convert it to :
    • Minutes if you divide by 60
    • Or Hours if you divide by 3600

    Dividing the Average by 3600 return the duration in hours. The decimal part is a fraction one an hour, therefore formatting 13,4867593 to hh:mm should return 13:29 not 13:32.

    An easy way to show seconds in hh:mm:ss format is to convert the seconds into duration by dividing by (24*60*60) then using the FORMAT function. unfortunately the hours over 24 will not appear but this is can be managed easily.

    I attached a pbix file where I provided some exemples on how return the duration in different string format

    ------------------------------
    If any discussion or answer help, please mark it as "Recommend"
    ------------------------------

    Best regards
    Mehdi HAMMADI

    MCT | MCSA BI Reporting | MCSE Data Management and Analytics | Data Analyst Associate
    2019 Ruby Award Dynamic Communities
    ------------------------------

    Attachment(s)

    pbix
    About Time format.pbix   25 KB 1 version


  • 8.  RE: How to display the table Total in grouped column chart

    Posted Sep 14, 2021 04:24 PM
    I think we are a little confused and we are complicating something so simple in my opinion.


    First I would like to know how I can get the total of these columns as it is done in the table, I understand that the first column is the sum of all the values ​​and that is how I obtain the result of 387.

    I say this because this is the total that I am interested in adding to the bar graph values.


    ------------------------------
    John Doe
    ------------------------------



  • 9.  RE: How to display the table Total in grouped column chart

    Top Contributor
    Posted Sep 14, 2021 05:25 PM
    First I would like to know how I can get the total of these columns as it is done in the table

    If you mean by "as it is done in the table" getting 4 bars in grouped column chart. According to your screenshot the answer is NO.

    It is important to make it clear that the columns from which the value is obtained 4:7 4:42 5:38 are columns made from a measure, that is, a DAX formula was used for its elaboration, for this reason I am not allowed to drag the column to the Values ​​section in the clustered column chart, every time I try to place it in Values ​​it automatically moves to Tooltip.
    The reason it moves to tooltip is because it is not recognized as a number. It is a text no way to put it in the values field.

    ------------------------------
    If any discussion or answer help, please mark it as "Recommend"
    ------------------------------

    Best regards
    Mehdi HAMMADI

    MCT | MCSA BI Reporting | MCSE Data Management and Analytics | Data Analyst Associate
    2019 Ruby Award Dynamic Communities
    ------------------------------



  • 10.  RE: How to display the table Total in grouped column chart

    Posted Sep 14, 2021 05:49 PM

    I understand that it is a text, now my other question is how we can convert to any type of data that is not text and that can be located in the value field.

    I have something like this done in a bar chart made in SSRS.




    ------------------------------
    John Doe
    ------------------------------



  • 11.  RE: How to display the table Total in grouped column chart

    Top Contributor
    Posted Sep 15, 2021 05:02 AM
    Regarding and considering the last screenshot maybe you can thing for something like this:

    Text can be split into two part regarding the ":" character
    and the metric can be : left_part *100 + righ_part

    Using the mesure you already provided try this
    Value =
    VAR _Avg = AVERAGE ( Consulta1[t_TOTAL_SALIDA_seg] )
    VAR _LeftPart = DIVIDE ( _Avg, 3600 )
    VAR _RightPart = MOD ( _Avg, 60 )
    RETURN
        _LeftPart * 100 + _RightPart
    


    ------------------------------
    If any discussion or answer help, please mark it as "Recommend"
    ------------------------------

    Best regards
    Mehdi HAMMADI

    MCT | MCSA BI Reporting | MCSE Data Management and Analytics | Data Analyst Associate
    2019 Ruby Award Dynamic Communities
    ------------------------------



  • 12.  RE: How to display the table Total in grouped column chart

    Posted Sep 15, 2021 09:43 AM

    It tries to perform the new measure that it tells me but I get a syntax error, I made the measure as it indicates to me as follows:

    NewMeasure = Value =
    VAR _Avg = AVERAGE ( Consulta1[t_TOTAL_SALIDA_seg] )
    VAR _LeftPart = DIVIDE ( _Avg, 3600 )
    VAR _RightPart = MOD ( _Avg, 60 )
    RETURN
    _LeftPart * 100 + _RightPart


    The following is the syntax error I get:

    The syntax for '=' is incorrect. (DAX(Value =VAR _Avg = AVERAGE ( Consulta1[t_TOTAL_SALIDA_seg] )VAR _LeftPart = DIVIDE ( _Avg, 3600 )VAR _RightPart = MOD ( _Avg, 60 )RETURN _LeftPart * 100 + _RightPart)).

    I attach an image of the measurement I am taking


    Any additional corrections that I should make?




    ------------------------------
    John Doe
    ------------------------------



  • 13.  RE: How to display the table Total in grouped column chart

    Top Contributor
    Posted Sep 15, 2021 05:41 PM
    My mistake Value is a reserved keyword
    try
    NewMeasure = 
    VAR _Avg = AVERAGE ( Consulta1[t_TOTAL_SALIDA_seg] )
    VAR _LeftPart = DIVIDE ( _Avg, 3600 )
    VAR _RightPart = MOD ( _Avg, 60 )
    RETURN
    _LeftPart * 100 + _RightPart​


    ------------------------------
    If any discussion or answer help, please mark it as "Recommend"
    ------------------------------

    Best regards
    Mehdi HAMMADI

    MCT | MCSA BI Reporting | MCSE Data Management and Analytics | Data Analyst Associate
    2019 Ruby Award Dynamic Communities
    ------------------------------



  • 14.  RE: How to display the table Total in grouped column chart

    Posted Sep 15, 2021 06:02 PM
    I have tried to perform the measurement as indicated in your last answer but I still do not obtain the exact result as represented by the total of the table.



    Your results are close but not exact to those shown in the table, besides that the character of ":"

    Are there any additional modifications that you should make to the extent that you shared with me?


    ------------------------------
    John Doe
    ------------------------------



  • 15.  RE: How to display the table Total in grouped column chart

    Top Contributor
    Posted Sep 16, 2021 06:31 AM
    Sorry, no more ideas

    ------------------------------
    If any discussion or answer help, please mark it as "Recommend"
    ------------------------------

    Best regards
    Mehdi HAMMADI

    MCT | MCSA BI Reporting | MCSE Data Management and Analytics | Data Analyst Associate
    2019 Ruby Award Dynamic Communities
    ------------------------------



  • 16.  RE: How to display the table Total in grouped column chart

    Posted Sep 17, 2021 09:24 AM
    Do not worry, thank you very much for your help, we were very close to achieving it, if you have another idea I will be pending and if you can help me get what I am looking for I will not hesitate to mark it as a "Recommend"

    ------------------------------
    John Doe
    ------------------------------



  • 17.  RE: How to display the table Total in grouped column chart

    Posted 28 days ago
    Edited by John Doe 28 days ago
    After a week of much research I had an idea I think I need to create an aggregate calculated table Summarize function and re-create my measures as calculated columns so that I can use it as values ​​in the column chart.

    I'm not sure the column chart will handle displaying your ratio values though - I think they'd be interpreted as text which will default to using a count aggregation when you add them to the visual. You might need to convert them as a percentage or decimal.

    Any suggestions regarding this? Do you think this is how I can get it?



    ------------------------------
    John Doe
    ------------------------------