Calculated column based on user entered figure

[BigNick1]

I have a PowerPivot table with masses of data and all the usual slicers/dimensions/drills but I have a dilemma. Basically our accountant wants an additional column which would be a calculated field which would use a manually entered and vaying percentage.

Hence the question, how do I get a manually entered figure into a powerpivot pivot table. I thought about creating a new SQL table with a range of percentages in but then couldn't figure how to combine the two unrelated data sources.

Thanks in anticipation,
Nick

 

[marco.russo]

If you create a table in PowerPivot with the possible values and you put it in a slicer, you can use it with the following formula (assuming you have a table called Simulation with a single column called Forecast containing 1 for 100%):

[Sales Forecast] := 
IF ( 
    HASONEVALUE ( Simulation[Forecast] ),
    SUM ( Sales[SalesAmount] ) / VALUES ( Simulation[Forecast] ),
    SUM ( Sales[SalesAmount] )
)

 

[BigNick1]

If you create a table in PowerPivot with the possible values and you put it in a slicer, you can use it with the following formula (assuming you have a table called Simulation with a single column called Forecast containing 1 for 100%):

[Sales Forecast] := 
IF ( 
    HASONEVALUE ( Simulation[Forecast] ),
    SUM ( Sales[SalesAmount] ) / VALUES ( Simulation[Forecast] ),
    SUM ( Sales[SalesAmount] )
)

Hi Marco

Many thanks for this but I'm afraid I don't understand how to apply it. Basically I've added a table into Sql with a single column called profpercent. This contains values ranging from 5-20. I have pulled the data back via PowerPivot and now have a slicer showing these values.

So from here how do I add the calculated column and use these percentages in the calculation? Ideally I need step-by-step instructions as, though I'm more than familiar with SQL, I'm afraid my PowerPivot and indeed pivot table skills in general are somewhat basic.

Thanks again,
Nick

 

[marco.russo]

Nick, you should crete a measure in your data model following the pattern above. I'd suggest you to look at documentation, at some video or book. It could be better for you understanding how a measure work in order to be able to use it in more situation, a Step-by-Step solution for your specific issue would be long for a message in the forum (there are many details required to write the correct formula) and not useful to other readers....

 

[gand3rson]

I would use the disconnected tables approach (rob collie approach check his book out)

Just create a one row data table in the excel file where the power pivot data exists. Call it "percentinput" It can have one column with the title "per" and underneath it the value your accountant wants to enter in.

Create a linked table in power pivot with this table.

Create a new measure [percentage]= min('percentageinput[per])


note u could use. Min,max,average, does not matter u just need to get around the "no naked columns rule" in Dax.

Use that new measure in your calculated column (in any table)

the accountant can changed the amount in the excel data table (the percentage rate), refresh the linked table in power pivot and refresh any pivot table and viola.