# Power Pivot - working with daily percentages

#### Escape777

##### New Member
Hi all,

I am fairly new to query and power pivot, but I have now been able to consolidate a couple of Excel data tables into a single database, using power query. Using power pivot I am able to run a daily report, as well as a monthly report. The daily reports have an actual percentage in the data. In the Power pivot table, once I filter per month, it wants to naturally either sum or average these values, which does not make sense, I am interested in the last value for the month, and the difference between this and the previous end of month value. i.e. the percentage change over the month.

I have tried using a calculated field to get the value for the last reported value for the month, but I am completely hopeless with DAX formulas for now...

My two questions are:
1. How can I get Power pivot to consolidate the percentages in the month filter, into the last value of the month?
2. If I need to do this outside the power pivot report, then how do I pull the correct val
3. ues out of the power pivot into a dashboard report?

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Escape777

##### New Member
Just a clarifying note on my previous post.

I am using a date table, with the data summarised per month. The idea would be that the percentages would summarise to the last recorded value in the month, while the rest of the data should be totalled together, as it is currently doing. I then want to use this number to calculate the difference between the two values at the end of the two adjacent months.

#### alansidman

##### Well-known Member
It would be very helpful if you showed us some data you are trying to pivot and manually mock up what the expected output should look like. Please use XL2BB to upload your data. Trying to visualize what you are asking can be eased with examples that actually show what you have. Not entirely sure, but you may need to build a measure to get what you want. Will know better once we see your examples.

#### Escape777

##### New Member
Hi Alan,

Thank you for the pointer. I hope this sheet provides the explanation that is required. In short, you can not sum percentages, but you can total numbers.

Regards,

Werner

Total Mine Balance Messed up Example Mr Excel .xlsx
ABCDEFGHIJK
15Row LabelsSum of Purchased Water Volume Sum of Disposed Water % Process Water Dam LevelSum of Rain Water (mm)
16Jun
176/26/20204500580050%6
186/27/20204410580082%2
196/28/20204410591665%7
206/29/20204365597482%3
216/30/20204410585860%9
247/2/20204410591657%7
257/3/20204680603223%1
267/4/20204590562667%5
277/5/20204725556859%0
287/6/20204455580089%7
297/7/20204590609086%8
307/8/20204275603298%6
317/9/2020436557428%2
327/10/20204320597478%8
337/11/20204590556877%8
347/12/20204365580085%1
357/13/20204410609080%7
367/14/20204500603286%6
377/15/20204320551036%6
387/16/2020468060320%2
397/17/20204500556873%8
407/18/20204320551078%2
417/19/20204410574247%7
427/20/20204365574229%5
437/21/20204410603220%9
447/22/20204410597478%2
457/23/20204365591637%7
467/24/20204680609014%6
477/25/20204635597439%3
487/26/20204320551074%10
497/27/20204275585847%9
507/28/20204365556863%7
517/29/20204320591641%4
527/30/20204320551034%10
537/31/20204275591681%2
568/2/20204320556820%5
578/3/20204410597438%0
588/4/20204545609041%2
598/5/20204635580068%8
608/6/20204500574231%9
618/7/20204590585869%2
628/8/20204500585885%0
638/9/20204590580087%9
648/10/20204635551017%5
658/11/20204725585860%9
668/12/20204680574290%0
678/13/20204455568465%5
688/14/20204275591611%3
698/15/20204725585865%8
708/16/2020450060326%5
718/17/20204500556859%2
728/18/20204545580063%4
738/19/20204500603229%9
748/20/20204320597424%8
758/21/20204365568492%0
768/22/20204635603218%0
778/23/20204590591648%5
788/24/20204635585888%7
798/25/20204455574247%1
808/26/20204365551099%0
818/27/20204725585892%3
828/28/20204365568422%8
838/29/20204320562627%9
848/30/20204320556848%3
858/31/20204680556839%1
88
89
90
91
93Sum of Purchased Water Volume Sum of Disposed Water % Process Water Dam LevelSum of Rain Water (mm)Accumulation or Depletion
94June 220952934860%28
95July 13774518043881%17522%
96August 13977017962681%1310%
Consolidated Database
Cell Formulas
RangeFormula
H22:K22,I86:K86,I54:K54H22=SUM(B17:B21)
H23:I23,K23H23=SUM(B17:B21)
J23J23=GETPIVOTDATA("[Measures].[Sum of Process Water Pond Level]",\$A\$15,"[Table1].[Date]","[Table1].[Date].&[2020-06-30T00:00:00]","[Table1].[Date (Month)]","[Table1].[Date (Month)].&[Jun]")
H54,H86H54=SUM(B23:B53)
H55:I55,K55,H87:I87,K87H55=SUM(B23:B53)
J55,J87J55=GETPIVOTDATA("[Measures].[Sum of Process Water Pond Level]",\$A\$15,"[Table1].[Date]","[Table1].[Date].&[2020-07-31T00:00:00]","[Table1].[Date (Month)]","[Table1].[Date (Month)].&[Jul]")
G94:J94G94=H23
G95:J95G95=H55
K95:K96K95=I95-I94
G96:J96G96=H87

Replies
1
Views
79
Replies
2
Views
60
Replies
0
Views
247
Replies
10
Views
204
Replies
32
Views
230

1,130,304
Messages
5,641,430
Members
417,209
Latest member
Agbarker

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back