Measure totals are doubling the value

[SandeA]

I am updating a spreadsheet table (data source) to add a new column of data and creating one new measure and updating 3 others with the new measure. Now, the pie charts with this data are showing double the amounts for ALL measures, even the ones I did not edit. Here my steps:

1. Went to Edit Queries and found that it needed to be reconnected to the updated sheet, did that.

2. Verified the table showed the same values as the spreadsheet for all columns and that the new column appeared and was in the correct format (whole number). Below is a screenshot of a part of the new data; the new column of data is the 3rd from the end (NRCS SOD EQIP Funds):

3. Clicked on Close and Apply

4. Went to the Data tab on the left and clicked on the Measures Table and clicked on New Measure

5. Created the new measure: Total NRCS = SUM('FundingSummary'[NRCS SOD EQIP Funds])

6. Updated a current measure used in a couple of pie charts to add the above measure: 

Total NRCS = SUM('FundingSummary'[NRCS SOD EQIP Funds])

7. Went back to the Report and now ALL the values in the pie chart are doubled!

This is what it was before any changes:

And this is what is shows now:

I have no idea why all of the values are doubling! Any help is appreciated!!

Sande

[SandeA · Accepted solution]

I went back and double (ok, probably quadruple at this point) checked my work and found that I was pulling the Total line from the spreadsheet into the query. Therefore, it was adding that amount to each column, thus doubling all the amounts. Duh... rookie mistake! LOL

[SandeA]

The FundingSummary Table is where all the data is stored from the spreadsheet. That data is used to create the Totals measures that are used in the pie chart measures. The Totals measures look like this (the value in the [ ] is the column header on the actual Excel spreadsheet):

Total USFS FHP = SUM('FundingSummary'[USFS FHP Grant])

Total USFS FHM = SUM('FundingSummary'[USFS FHM Grant])

Total USFS Land = SUM('FundingSummary'[USFS-Eradication on USFS land and local admin*])

Total USFS R5 = SUM(FundingSummary[USFS R-5 FHP Monitor and Detect ])

Total BLM Land = SUM(FundingSummary[ BLM-Eradication on BLM Land ])

Total BLM Survey = SUM(FundingSummary[BLM Survey/Treatment Funds to ODF])

ODF General Funds = [Total ODF Brkngs]+[Total ODF NonFed]

       Total ODF Brkngs = SUM('FundingSummary'[ODF-Pathologist and Brookings Office GF])

       Total ODF NonFed = SUM('FundingSummary'[ODF-Eradication on non-Federal land])

Other State-OPRD, ODOT, OISC = [Total ODOT]+[Total State Parks]+[Total OISC]

       Total ODOT = SUM('FundingSummary'[ODOT-Eradication on ODOT land])

       Total State Parks = SUM('FundingSummary'[State Parks-Eradication on State Parks Land])

       Total OISC = SUM('FundingSummary'[OISC Eradication funds to ODF])

Total USDA APHIS = SUM('FundingSummary'[USDA APHIS-Eradication funds to ODF])

Total USDA APHIS Survey = SUM(FundingSummary[USDA APHIS-Funds to ODF for Survey ])

Total InKind IND = SUM('FundingSummary'[Industry In-Kind***])

Total InKind Priv = SUM(FundingSummary[PRIVATE-In-kind Contributions****])

The only change I made is to add the Total NRCS measure; I didn't do anything with the above measures.

New measure: Total NRCS = SUM('FundingSummary'[NRCS SOD EQIP Funds])

The pie chart uses these measures (these are before I added the new data):

USFS = [Total USFS FHP]+[Total USFS FHM]+[Total USFS Land]+[Total USFS R5]

BLM = [Total BLM Land]+[Total BLM Survey]

ODF/State = [ODF General Funds]+[Other State-OPRD, ODOT, OISC]

USDA APHIS = [Total USDA APHIS]+[Total USDA APHIS Survey]

USFS ARRA = SUM('FundingSummary'[USFS ARRA to ODF])

Private = [Total InKind IND]+[Total InKind Priv]

The only difference was for the USFS APHIS. The edited measure now looks like this:

USDA APHIS & NRCS = [Total USDA APHIS]+[Total USDA APHIS Survey]+[Total NRCS]

Thank you!

[Icey]

Hi @SandeA ,

The meausre "USDA APHIS & NRCS" has a part more than "USFS APHIS": [Total NRCS]. This is most likely the cause. 

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[AllisonKennedy]

What measure was it using before? How is the FundingSummary table related to the other columns used in the pie chart?

When you say: 

---

@SandeA wrote:

 Now, the pie charts with this data are showing double the amounts for ALL measures, even the ones I did not edit. Here my steps:

---

What do you mean by 'even the ones I did not edit?'