Sum of Measure is wrong

[JPY]

Hi all,

I was trying to come up with a measure to sum up the amount. It returns the correct number for each row, however, the total is not correct. Does anyone know how to go about doing it?

Measure = IF(DISTINCTCOUNT('Table'[Merged])>1,DIVIDE(sum('Table'[Amount]),DISTINCTCOUNT('Table'[Merged])),SUM('Table'[Amount]))

Table:

Amount	Booked	Merged
1734887.59	32148653	2016-38736 1 001001006
1734887.59	32148653	2016-38737 1 001001006
484259	32148944	2016-39663 1 001013000
2656296	32175943	2017-103708-3 1 001001020
2261870	32175948	2017-103708-3 2 S001001002

Here is the link of the pbix sample: 

https://drive.google.com/open?id=10hGBZUKJ2ud9CtUpjOs6WQIQvKMvgaZw

Thank you!

JPY

[v-jayw-msft]

Hi @JPY ,

The total isn't the sum of the measure, it's the result of all amount being calculated by your formula.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[richbenmintz]

Hi @JPY ,

I think this is an issue of granularity of the data at the row level of the table the data is calculated correctly, however at the grand total the distinct counts are all 1, if you create t following measures you will see how you can make the total work properly with sumx

dist_count = DISTINCTCOUNT('Table'[Merged])

divide_by_dist = if(DISTINCTCOUNT('Table'[Booked]) = 1, [Measure], sumx(VALUES('Table'[Booked]),[measure])/[dist_count])

[amitchandak]

Try

Measure =
sumx(summarize('Table','Table'[Booked],"_sum",sum('Table'[Amount]),"_dist",DISTINCTCOUNT('Table'[Merged]))
	if(_dist>1 , divide(_sum,_dist),_sum))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

Hi, I understand your confusion. 

The calculation for total is represented by your measure: 

DIVIDE ( 8.872.200,18 / 5 ) 

Gives you 1.774.440,04; which is correct! 

Basically adding your original "Amount", will help here to understand your problem.