Using a Grand total % of one measure in another measure

[Sonova]

Hi is it possible to use the grand total of a measure in another measure calculation.

For example , in the table provided I want to multiply my FCST column by the grand total % in my Measure 1 column (22.24%) and the % ALP column ( 12.99%).(Measure 2)

https://www.dropbox.com/s/fdibhhav087vf6r/table.png?dl=0

 

[akice]

Absolutely. You just need to know what level of granularity you want the grand total to equal, then use of the "ALL" functions to compute just that amount. Grand totals are just another cell to a power pivot table that just has less column filters on it. For example, in your pivot the grand total for the year is calculated without filters on the month columns; there is only a filter on the year column. Something like:

CALCULATE ( [Measure 1], ALL ( Calendar[Month] ) )

 

[Sonova]

Absolutely. You just need to know what level of granularity you want the grand total to equal, then use of the "ALL" functions to compute just that amount. Grand totals are just another cell to a power pivot table that just has less column filters on it. For example, in your pivot the grand total for the year is calculated without filters on the month columns; there is only a filter on the year column. Something like:

CALCULATE ( [Measure 1], ALL ( Calendar[Month] ) )

Akice thanks for the reply, I have tried the all function but the formula still ends up using the % for that specific period.
https://www.dropbox.com/s/wp8xib86xtdihdq/updatedtable.png?dl=0


I also wanted to ask as I am using an if formula to identify where the FCT column =0 to perform the calculation but this is not ideal as it could work out amounts for retrospective periods. I have a last actual date formula and tried to use an if to identify where my calendar month is greater than the last actual and then apply the formula but it produced an error. (contains a value without aggregation)

Not sure where I am going wrong with these.



Measure 2

Measure 2:=calculate([Measure 1],all('Calendar'[Date]))

Projection

Projection:=if([FCST]=0,[FCST]+([FCST]*[Measure 2]),0)

If Formula to Identify Dates after last actual

Last Actual

Last Actual:=CALCULATE( LASTDATE( MonthlyData[StartOfMonth] ) , FILTER( ALL( MonthlyData ), NOT( ISBLANK( MonthlyData[Actual] ) ) ) , ALL( Categories ) )

Projection with if for dates after last actual

Projection:=if(MonthlyData[StartOfMonth]>[Last Actual],[SumFCSTT]+([Sum FCST]*[Measure 2]),0)

 

[Safety Guy]

It looks like the two measures you want to multiply are in the Grand Total row. If they are, you won't be able to use that number since it is a product of the pivot table and not a measure or product of the data model. The Grand Total row can be turned off in the "Design" ribbon of PivotTable Tools. I'm pretty sure (I'm fairly new to this, but don't tell anyone!) if you need to use those numbers in a calculation, you need to make them a measure first. Or I could be completely wrong. In that case, disregard this entire post!