cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tanmaym93
Frequent Visitor

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 follows:

1st Row in the table (irrespective of the month) - 1000/1

2nd Row in the table - (1000+1500)/2

3rd Row - (1000+1250+1335)/2

so on and so forth...

 

Is there a way to get this? I am assuming this might be a simple DAX, but i am quite new to this.

 

Note: Not sure if this helps but I have a column in my data set called "HC" which has the value "1" for each row. The sum of this for each month is what reflects in HC column in the below table.

 

tanmaym93_0-1657688233779.png

 

Unfortunately, as the data is company sensitive I am unable to share the screenshot of the table visualization that I have built in PBI.

 

Appreciate your help and support! Thanks!

8 REPLIES 8
tanmaym93
Frequent Visitor

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.

 

tanmaym93_0-1658124518437.png

 

tanmaym93_1-1658124576945.png

 

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.

Hi @tanmaym93 ,

 

I am not sure I have understood what you mean.

so this HC value doesn't the actual data?

vmengzhumsft_0-1658196843697.png

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

vmengzhumsft_1-1658196992887.png

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

 

Hi Selina - My apologies for any confusion,

 

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

 

tanmaym93_0-1658220554898.png

 

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

 

 

 

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

 

 

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

 

tanmaym93_0-1658399203996.pngtanmaym93_1-1658399215704.png

 

tanmaym93_2-1658399246266.png

tanmaym93_3-1658399253993.png

 

tanmaym93_4-1658399265035.png

 

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%:

 

tanmaym93_5-1658400810688.png

YTD% will be Cumulative Exit/Cumulative Average Headcount.

 

I am not sure, but does this help?

 

Thanks & Best Regards

Tanmay Mukherjee

Hi @tanmaym93 ,

 

Sorry for the late reply, thank you for your very clear description this time ~ it made me understand what you want to achieve.🤗

 

Did you get your problem solved?

 

I wonder if it would be convenient for you to give me a sample data or preferably a PBIX file (without private information), so that it would be easier for me to do the calculation for you.

 

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

Hi Selina - Apologies for the delay, I am unable to attach any file as I et an error that the file extension is not supported (slsx or pbix).. However, I can share a snippet, woud that help?


Thanks & Best Regards

Tanmay Mukherjee

tanmaym93_0-1660206907892.png

 

v-mengzhu-msft
Community Support
Community Support

Hi @tanmaym93 ,

 

Is this result you want to achieve?

vmengzhumsft_0-1657877990018.png

If it is, you can according to my testing process:

For easy calculation and clear understanding, I create an index column and two measures

  1. For index column, you can add it in power query
  2. The first measure is to calculate the Cumulative value:

           You can try this measure:

  Cumulative value = var _hc=SELECTEDVALUE('Table'[HC])
  return SUMX(FILTER(ALL('Table'),'Table'[HC]<=_hc),'Table'[HC])

 

then you can get the Cumulative value:

vmengzhumsft_1-1657877990020.png

    3.  The second measure is what you want to calculate—the average of HC

         You can use this measure:

  avg hc = 'Table'[Cumulative value]/SELECTEDVALUE('Table'[Index])

 

and finally , the result is as the above shows

 

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.