Wrong Totals after unpivoting some columns

[Anonymous]

HI Team,

i unpivoted 5 columns into one column namely agetype and value. 

I am getting wrong totals for products as shown in the image. it is calculating 5 times the total ap balance for a given vendor, thereby giving wrong totals. 

please advice on how to resolve this. 

[banjofork]

Same issue, but in my case the instead of numbers i have a string and the calculation doesn't work any tips ?

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

For your case, you could try this way:

this is my basic sample data

Now I unpivot the columns(Type1...Type5)

Now use AVERAGE and SUMX Function to create a measure like this:

Measure = var _table=SUMMARIZE('Table','Table'[Vendor]) return
SUMX(_table,AVERAGE('Table'[Qty]))

Result:

Here is my sample pbix file, please try it.

If it is not your case, please share sample pbix file and expected output . You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

By the way: there is a measure totals knowledge for this case. See the post about it here:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Best Regards,

Lin

[Anonymous]

Thanks Lin @v-lili6-msft 

This worked for me !! thanks for your example; which made it easier to understand. 

Regards,

Ronak

[Anonymous]

Thanks Lin @v-lili6-msft 

This worked for me !! thanks for your example; which made it easier to understand. 

Regards,

Ronak