Issue with Totals

[salman_ali]

Hi team, this is the issue I am having

Here below is my query

km/stop =

VAR AVGKMS = AVERAGE(E3table_1[KM])

return

if(hasonevalue(E3table_1[ManInd]),AVGKMS,SUMX(VALUES(E3table_1[ManInd]),AVGKMS))

the output I am getting is 

Total Manifest = 4

Avg Km/shift = 320.86

Count = 43

Total Avg KM/Shift (AVGKMS)- 1,283.43

sorry I cannot print screen as it is too small.  

Below is what I want to see (This below is when I did this on excel)

As you can see below 

my 4 rows on manifest is correct as above

my total counts of 43 is also correct, 

my average of KMS is also correct.

Row Labels	Average of KM	Count of Stops
4583383	350.83612	11
4623383	408.77236	9
4633383	347.61744	10
4643383	214.04222	13
Grand Total	320.8574842	43
sum 4 avg kms	1321.26814
sum divided by count	30.72716605

The discrepancy is that when i run above dax formula I want the sum to be 1321.268 as below, but it is coming to 1,283.43, and I have no idea where I am getting that from.  can anyone help? 

[v-frfei-msft]

Hi @salman_ali ,

You can copy and past your sample data here directly. Could you plz share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive for Business and share the link here.

[Ashish_Mathur]

Hi,

Do these measures work?

AVGKMS = AVERAGE(E3table_1[KM])

Measure1 = if(hasonevalue(E3table_1[ManInd]),AVGKMS,SUMX(SUMMARIZE(VALUES(E3table_1[ManInd]),E3table_1[ManInd],"ABCD",[AVGKMS]),[ABCD]))

Hope this helps.

[salman_ali]

Sorry Ashish, this is giving me error 

[Ashish_Mathur]

Hi,

Does this work?

AVGKMS = AVERAGE(E3table_1[KM])

Measure1 = if(hasonevalue(E3table_1[ManInd]),[AVGKMS],SUMX(SUMMARIZE(VALUES(E3table_1[ManInd]),E3table_1[ManInd],"ABCD",[AVGKMS]),[ABCD]))

If it does not, then share the link from where i can download the PBI file and clearly show the visual where my measure is not working.

[salman_ali]

sorry unfortunately i cannot share the file but the error comes when i type Measure1 =.

does there need to be a something after 

AVGKMS = AVERAGE(E3table_1[KM]) to connect the two lines?

[Ashish_Mathur]

Hi,

Sorry but i cannot help if you cannot share any data.

[salman_ali]

i understand, thank you 

[parry2k · Accepted solution]

@salman_ali these will be two different measures , one Avg Kms Measure and 2nd will be Measure1

AVG Kms Measure = AVERAGE(E3table_1[KM])

Measure1 = 
IF ( HASONEVALUE(E3table_1[ManInd]),
[AVG Kms Measure],
SUMX(
SUMMARIZE ( VALUES(E3table_1[ManInd]), E3table_1[ManInd], "AvgKms",[AVG Kms Measure] ),
[AvgKms]
)
)

[salman_ali]

Thank you, this worked