Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 LabelsAverage of KMCount of Stops
4583383350.8361211
4623383408.772369
4633383347.6174410
4643383214.0422213
Grand Total320.857484243
sum 4 avg kms1321.26814 
sum divided by count30.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? 
 
 
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous 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]
)
)

     

9 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Sorry Ashish, this is giving me error 

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        6 years ago

        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.

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    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.