Forum Discussion

tanmaym93's avatar
tanmaym93
Frequent Visitor
4 years ago

Average Calculation in Table Visual

Hi Community - I am trying to obtain the Average Headcount (in Average HC) column in a table visualization; however I need some help with the calculation.  The average HC will be calculated as follo...
Need Help
tanmaym93's avatar
tanmaym93
Frequent Visitor
4 years ago

Hi Selina - This is the result I am looking to achieve, however my HC value is calculated as a Sum of the HC column that I have in my dataset..

My dataset has 18k rows and I have assigned the value "1" for all the rows.

 

 

 

As a result, I am unable to obtain the result through the Cumulative value measure you suggested.

 

Let me know if you need additional information from me that can assist you.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi tanmaym93 ，

 

I am not sure I have understood what you mean.

so this HC value doesn't the actual data?

and this is the new HC value which needs to be calculated average value?

If it is convenient for you, you can send me a sample data

 

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

  • tanmaym93's avatar
    tanmaym93
    Frequent Visitor
    4 years ago

    Hi Selina - My apologies for any confusion,

     

    Here is the redacted data subset from my PBI data table.

     

     

    Basically, I have appended 2 sets of data - 1 is the Headcount report and 1 is the Exit report.

    I have then added a column called HC_New which returns the value of 1 if the row comes from the Headcount report and 0 if not.

    Similarly I have added a column called Exit_New which returns the value of 1 if the row comes from the Exit report and 0 if not.

    Using this, I have sumof HC_New which gives me the month on month HC total..

    and sumof Exit_new which gives me the month on month Exit total...

     

    Best Regards

    Tanmay Mukherjee

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi tanmaym93 ，

       

      Thank you for your detailed description, I now have a general understanding of the construction of your table, can you please tell me more about the specific calculations you are currently trying to complete?For example, what data do you want to use to get a result?

       

      Best regards,

      Community Support Team Selina zhu

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

       

       

      • tanmaym93's avatar
        tanmaym93
        Frequent Visitor
        4 years ago

        Hi Selina - Great! So now, in terms of what data I would like to use - I am using the HC_New & Exit_New fields to calculate my results.....

         

         

         

         

        My table visualization looks like this and I am looking to add 2 more columns to this table - Cumulative Avg Headcount (as per my initial post) & YTD%:

         

        YTD% will be Cumulative Exit/Cumulative Average Headcount.

         

        I am not sure, but does this help?

         

        Thanks & Best Regards

        Tanmay Mukherjee