Power Pivot - working with daily percentages

Escape777

New Member
Joined
May 6, 2008
Messages
42
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?
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Escape777

New Member
Joined
May 6, 2008
Messages
42
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
Joined
Feb 26, 2007
Messages
6,158
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 6, 2008
Messages
42
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
22JulCurrent Answer2209529348340%27.6
237/1/20204500580081%9Required Answer220952934860%27.6
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
54AugCurrent Answer13774528768266%32.5
558/1/20204365591679%1Required Answer13774518043881%175
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
86Grand Total2996103894123737%334Current Answer13977028304229%23.9
87Required Answer13977017962681%131
88
89
90
91
92Dashboard Answers
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,514
Messages
5,625,245
Members
416,085
Latest member
Jlex

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top