cancel
Showing results for
Did you mean:
Highlighted
Helper III

## Issue with Totals

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV

@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]
)
)``````

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

9 REPLIES 9
Highlighted
Super User III

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted
Helper III

Sorry Ashish, this is giving me error

Highlighted
Super User III

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted
Helper III

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?
Highlighted
Super User III

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted
Helper III

i understand, thank you

Highlighted
Super User IV

@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]
)
)``````

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Community Support

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.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Highlighted
Helper III

Thank you, this worked

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors