Total of measure is incorrect

[Anonymous]

Hello, I am trying to get a "total of cost variance". At row level I get the correct cost variation but the total does not add up. I refered to couple of other post with similar issue but my case is different since my measure has a combination of average and substraction. I have pasted the data table here. Also, in 2nd table, the highlightes area is where the issue is. I am using following to DAX calculations. FIrst one to get just average variance per truck and 2nd to create master meaure column to use in my view.

Extra TL Cost = Calculate(AVERAGE('PTA'[FreightCost]),FILTER(ALL('PTA'[Executed Mode]),PTA[Executed Mode]="TL"))-Calculate(AVERAGE(PTA[FreightCost]),FILTER(ALL(PTA[Executed Mode]),PTA[Executed Mode]="IM"))

Total TL Variance = if(HASONEFILTER(PTA[Lane]),Calculate(DISTINCTCOUNT(PTA[LOADNUM]),filter(ALL(PTA[Executed Mode]),'PTA'[Executed Mode]="TL"))*[Extra TL Cost],sumx(PTA,[Extra TL Cost]))

 

[v-eachen-msft]

Hi @Anonymous ,

You could use ISINSCOPE() function to set the value of total row.

Could you please share your sample data and excepted result to me if you don't have any confidential information. Please upload your files to One Drive and share the link here.

[amitchandak]

 If possible please share a sample pbix file after removing sensitive information.

You are using distinctcount in one formula and distinct count at GT level might not sum of the line level. so can show diff.

Thanks

Read and like My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[rajulshah · Accepted solution]

Hello @Anonymous,

You can try using the following measure to plot in the matrix:

Total Measure = SUMX(SUMMARIZE(PTA,PTA[Lane]),[Total TL Variance])

Hope this helps.