sum the value in summarized colu

[Anonymous]

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.

[Farhan75]

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]

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:

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

[Farhan75]

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) 

[v-juanli-msft]

Hi @Anonymous

As tesetd, the output is 19.5

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

Best Regards

Maggie

[Anonymous]

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

[v-juanli-msft · Accepted solution]

Hi @Anonymous

Does this meet your requirement?

If it helps you, could you accept the answer?

Best Regards

Maggie