Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

sum the value in summarized colu

I would like to sum the value or all rows summarized column first and want this type of output as mentioned in below picture.

 

 

As mentioned in image:
I want to show the Toal of (Avg Stay Time) Column in Card . and after that I would like tot device that total with Count of Unique booth name. In this case uquine count of booth name is 4. But i want it to change when data is changed.

Output should be 79 (13+5+18+43)/4= 19.75 or 20

 

Thanks in advance.

Need Help

6 Replies

  • Farhan75's avatar
    Farhan75
    Frequent Visitor
    8 years ago

    Hi

     

    Of course, you understand that the Total Avg Stay Time you're currently getting is a Weighted Average of your underlying data. If you want a straight average of your aggregated results, then you could achieve that using a SUMMARIZE function as below:

     

    Say you're current Measure is something like:

    Avg Stay Time = SUM(Table1[Duration Seconds])/COUNT(Table1[Badge ID])/60

     

    Then to get the Total you're after

    Measure = SUMX(SUMMARIZE(Table1,Table1[Booth Name],"Avg Stay Time",[Avg Stay Time]),[Avg Stay Time])/DISTINCTCOUNT(Table1[Booth Name])

     

    This gives 19.73.

     

    Hope that helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago
      It's shwoing 19.5 instead of 19.73. Am i Doing Right?

      I have used following Mesure function

      AvgStayTime_SUMX = SUMX(SUMMARIZE(Table1,Table1[Booth Name],"Avg Stay Time",[AvgStayTime_DIVIDE]),[Avg Stay Time])/DISTINCTCOUNT(Table1[Booth Name])

      And my Avg Stay Tim mesure having this formula:

      AvgStayTime_DIVIDE = INT( 
DIVIDE(
	SUM('Table1'[Duration Seconds]),
	DISTINCTCOUNT('Table1'[BadgeID])
)/60)

      Actual table is this:

      BadgeIDJob TitleFirstNameLastNameBoothNumberBooth NameDuration Seconds
      123EngineerabcWoods27Booth 1600
      456EngineerdefWoods27Booth 1480
      789EngineerghiSa27Booth 11320
      532Chief Information OfficerjklVa14Booth 2240
      1182304Operations ManagerChetManchester21Booth 3980
      1182523Account ManagerTravisFulton21Booth 31080
      1182657Senior EngineerLuisaSangines21Booth 31680
      1182879EngineerMarkSkallet21Booth 3480
      1149995AnalystMeganHemmila37Booth 41200
      1149995AnalystMeganHemmila37Booth 42280
      1150180Architect|PartnerHoaTram37Booth 41200
      1150180Architect|PartnerHoaTram37Booth 4480
      117Senior EngineerpqrPa14Booth 2360

       

       

      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        8 years ago

        Hi Anonymous

        As tesetd, the output is 19.5

        If this doesn't meet your requirement, please contact with me.

         

        Best Regards

        Maggie