Pivot running total and calculated field

[droopy]

Hello,
I am trying to show forecasts with cumulated data.
My data table is as follows:

ID - unique identifier
A Revenue - number or "-" if empty
A Margin - number or "-" if empty
A Margin% - formula: =iferror([A Margin]/[A Revenue],"-")
B Revenue - number or "-" if empty
B Margin - number or "-" if empty
B Margin % - formula: =iferror([B Margin]/[B Revenue],"-")
Fiscal Quarter - text, formatted as FYxx-Qx​

I make a pivot with Fiscal Quarter as Rows sorted Z-A, and all the other data as values:

Count of ID, Running Total of Fiscal Quarter for A Revenue, A Margin, B Revenue, B Margin, so as to see the forecasts cumulated through the quarters, average for A Margin % and for B Margin %.​

For example, FY17-Q1 will show the total amounts (revenues and margins) of what is ahead of us starting from Q1 of FY17.
Similarly, FY18-Q2 will total the amounts of what is due to complete from Q2 FY18 onward, ignoring what is due to complete before.

I can have values in A-type columns and not in B-type columns or the other way around, or in both A-type and B-type columns.

My problems come with the margin percentages. I cannot use running totals here, as it is completely meaningless. What I actually need, is that the margin % reflects the [running total of A (resp. B) Margin]/[running total of A (resp. B)Revenue] of the corresponding row.
But with the average of margin %, it only gives me the average of margin % of the corresponding quarter, not of all the quarters onward.

I tried to upload a sample file, but for some reasons, it does not see my excel files, although it properly sees Word documents or pdf etc.

I thought using powerpivot as well, but it does not like mixing types (e.g. number and text, or date and text), which I have in the raw data.

Any help would be welcome!

 

[droopy]

Well, my browser was was causing the issue with the file type. I took another browser, and now it's ok.
Please see attached.

 

[Chihiro]

Your table structure isn't suited for PivotTable be it standard pivot or PowerPivot.

I'd recommend creating flat table and using that as basis of your analysis. As well, your data source looks like it's aggregated from raw data. It'd help if you upload raw data structure (it's probably going to be easier to do analysis on it).

Then you can use PowerPivot with DAX measures to accomplish what you are looking to do.

 

[droopy]

Hi Chihiro,

I receive the data this way (with more columns indeed), except the calculated columns (margin %), and the fiscal quarter which is actually a lookup on a dimdate table where the mapping is done, based on an end date in the raw data.
Anyway, let's assume the raw data are only:
ID, A Rev$, A EGM$, B Rev$, B EGM$, end date.
With any of A Rev$, A EGM$, B Rev$, B EGM$ potentially empty ("-", indeed).
From the end date, I can derive the quarter, and from the A and B EGM$ and Rev$ columns, I can derive the A and B margin %.
The outcome I want is the cumulative of each Rev$ and EGM$ from the considered quarter onward, and the margins calculated on these running total (as EGM$/Rev$).

When I tried to do this with powerpivot, my issue was that I have both numbers and text ("-") in the same column, which powerpivot does not like at all. Otherwise, powerpivot and dax would be my preferred method.

I attached a new version of the file which better reflects the reality.

 

[Chihiro]

You can replace "-" with 0. Or with null value.

PowerPivot will be able to deal with it then.

In regular pivot, you can't do Running Total on Average value. Or you are going to get weird results. This is due to the fact, Pivot has to do average on each row label and then does sum calculation for running total. Nor can you create calculated field for this, as it's only able to return result for each Row Label grouping.

Give me bit of time. I'll see if I can give you PowerPivot sample.

 

[Chihiro]

Few questions...

Why is your running total done backwards?

I mean why for FY17-Q2 you are summing upto and including FY19 & beyond?

Also, why is FY18-Q4 filtered out?

I'm bit perplexed as to what this data should represent.

 

[droopy]

Hi Chihiro,

Thank you for your response.

FY18Q4 should not be filtered out. That's just a mistake.

And the reason why I sum backward is because I have ongoing deals as of today. They have an expected completion date in the future.
As I am in FY17 Q2, I want to have the volume of what is ahead of me, thus from now on up to the last completion date.
But I also want to see what the future looks like. So if I have plenty of deals closing in the next 2 or 3 quarters, and almost nothing after that, I probably want to go back to the sales rep, because soon I will have no more revenue (based on the length of our sales cycles).

I guess you get the idea, now.

I was kind of reluctant of using 0, because 0 can actually be 0, or an empty cell.
Also when it comes to dates, it translate in Jan 0 1900. I guess I'll have to exclude this value from the powerpivot result.