cancel
Showing results for
Did you mean:
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous

If it helps you, could you accept the answer?

Best Regards

Maggie

6 REPLIES 6
Frequent Visitor

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
Not applicable
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]),
)/60)```

Actual table is this:

 BadgeID Job Title FirstName LastName BoothNumber Booth Name Duration Seconds 123 Engineer abc Woods 27 Booth 1 600 456 Engineer def Woods 27 Booth 1 480 789 Engineer ghi Sa 27 Booth 1 1320 532 Chief Information Officer jkl Va 14 Booth 2 240 1182304 Operations Manager Chet Manchester 21 Booth 3 980 1182523 Account Manager Travis Fulton 21 Booth 3 1080 1182657 Senior Engineer Luisa Sangines 21 Booth 3 1680 1182879 Engineer Mark Skallet 21 Booth 3 480 1149995 Analyst Megan Hemmila 37 Booth 4 1200 1149995 Analyst Megan Hemmila 37 Booth 4 2280 1150180 Architect|Partner Hoa Tram 37 Booth 4 1200 1150180 Architect|Partner Hoa Tram 37 Booth 4 480 117 Senior Engineer pqr Pa 14 Booth 2 360

Frequent Visitor

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)

Community Support

Hi @Anonymous

As tesetd, the output is 19.5

Best Regards

Maggie

Anonymous
Not applicable

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

Community Support

Hi @Anonymous

If it helps you, could you accept the answer?

Best Regards

Maggie

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!