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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi Anonymous

    Does this meet your requirement?

    If it helps you, could you accept the answer?

     

    Best Regards

    Maggie

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi v-juanli-msft, Thank you for your reply on this. Culd you please share the PBIX file with me?

       

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

        Hi Anonymous

        Does this meet your requirement?

        If it helps you, could you accept the answer?

         

        Best Regards

        Maggie

  • Farhan75's avatar
    Farhan75
    Frequent Visitor
    8 years ago

    Hi

     

    It's because you're using the INT function in your AvgStayTime_DIVIDE which is rounding your results down. So, for example, against Booth 1 you're getting 13 instead of 13.33.  This is then affecting you're overall average.

     

    If you want to round down (for some reason) then what you're doing is correct. If you just want to display you're results as rounded down, then I would remove the INT function.  You can change how your measure is displayed in the Modelling tab, or in the 

     

    ** apologies for delay in responding. I tried to reply after your initial reply, but it disappeared (perhaps when you were editing it)