Use the displayed value (Show Values As) in a New Measure

[loaizaprat]

I have a PowerPivot table in which one of the columns is displayed as % of Parent Row Total. I want to take that % and use it in a New Measure for the PowerPivot Table.

For instance:

Field 1 Value Field 1 Showed As % Field 2
Row 1 10 25% 100

I want a New Measure that multiples 100 * 0.25 but all I get is 100 * 10.

Any ideas will be appreciated.

Thanks in advance.

 

[loaizaprat]

I realize the data example is not very clear because of the formatting, so I'll put it again here, hoping it's more clear:

--------"Field 1 Value"---- "Field 1 Showed As %"----- "Field 2"
Row 1______ 10 ______________ 25% ______________100

I want a New Measure that multiples 100 * 0.25 but all I get is 100 * 10.

 

[scottsen]

Nope. The "Show Value As" is kinda of a display only trick that only Excel knows about. PowerPivot has no idea what values are being displayed. You will need to write your measure to "directly" do the work you want.

 

[loaizaprat]

Nope. The "Show Value As" is kinda of a display only trick that only Excel knows about. PowerPivot has no idea what values are being displayed. You will need to write your measure to "directly" do the work you want.

Thanks for your reply scottsen. Any idea on how to create a measure that calculates the same displayed value I'm talking about?

 

[scottsen]

It's going to depend on your models/measures. You want to sharepoint a workbook and I can take a look?

 

[loaizaprat]

It's going to depend on your models/measures. You want to sharepoint a workbook and I can take a look?

Mmmm, I don't think I know how to share a file...

 

[scottsen]

Wow, I did I really type "sharepoint a workbook". lol. I think most people here post a link to the file on google docs?

 

[loaizaprat]

Wow, I did I really type "sharepoint a workbook". lol. I think most people here post a link to the file on google docs?

Thanks scottsen, here's a link you can use to access the file: http://1drv.ms/1jdDU99 . What I want to accomplish on the PowerPivot table on Sheet "Points", is a new measure with the following formula:


Table1[Sum of Points] * Table1[Average of Response SLA] * Table1[Average of Restore SLA] * % Tickets Closed

 

[scottsen]

I really wish I could give you an "easy" answer, but alas... it is somewhat complicated.

So, the count of incidents is just: counta(Table1[Incident #])

You would kind of like to do something like... the simple division of THIS incident count to ALL incident count:
=counta(Table1[Incident #]) / CALCULATE(counta(Table1[Incident #]), ALL(Table1))

But that is always calculating the % of TOTAL, not the % of Parent. Unfortunately, you have 2 "levels" of parents... so, you need different calculations for each level, using IF() to decide.

Here is your final version. I think.

=if(HASONEFILTER(Table1[Team - Assignee]), 
       counta(Table1[Incident #]) / CALCULATE(counta(Table1[Incident #]), ALL(Table1[Team - Assignee])),
       counta(Table1[Incident #]) / CALCULATE(counta(Table1[Incident #]), ALL(Table1[Assignment Team]))
)

 

[loaizaprat]

I really wish I could give you an "easy" answer, but alas... it is somewhat complicated.

So, the count of incidents is just: counta(Table1[Incident #])

You would kind of like to do something like... the simple division of THIS incident count to ALL incident count:
=counta(Table1[Incident #]) / CALCULATE(counta(Table1[Incident #]), ALL(Table1))

But that is always calculating the % of TOTAL, not the % of Parent. Unfortunately, you have 2 "levels" of parents... so, you need different calculations for each level, using IF() to decide.

Here is your final version. I think.

=if(HASONEFILTER(Table1[Team - Assignee]), 
       counta(Table1[Incident #]) / CALCULATE(counta(Table1[Incident #]), ALL(Table1[Team - Assignee])),
       counta(Table1[Incident #]) / CALCULATE(counta(Table1[Incident #]), ALL(Table1[Assignment Team]))
)

Hello Scott.

I only had the chance today to review what you sent. Thank you very much.

I got the following error when Checking the formula: Failed to resolve name 'HASONEFILTER'. It is not a valid table or a function name.

Any ideas?